Dynamic date setting in loops

Hello everybody,

I've been trying to write a script to populate a table.
One filed is of 'date' type and I would like to insert dates different from record to record.
I thought about creating a loop and then try to increment the day (or the hour, I don'care) by using the loop index.
There comes of course a problem of casting from integers to strings (or date).

I tried to do something like:

DECLARE @.K INT
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES ('20071026 11:'||CAST(@.K AS VARCHAR)||'00')
SET @.K=@.K+1
END

but it didn't work ...

Would you please suggest a method of performing this action?

Thanks in advance,

Stefano.What is the data that you are trying to insert. I dont think the initial condition of the loop satisfies at all.|||The data type is 'datetime' and I was trying to build the string by a cat operation.
For instance, to build '20071025 12:28:40' I coded:

'20071025 12:'+cast(@.j+28, varchar)+':40'

where @.j is the loop variable.

The aim is to obtain strings with dates like:

..............................
'20071025 12:29:40'
'20071025 12:30:40'
'20071025 12:31:40'
'20071025 12:32:40'
'20071025 12:33:40'

and so on ...|||If you have the table like below:
TABLE1 ([DATETIME] DATETIME)

The modification your script to correct one as follows:

DECLARE @.K INT
SET @.K = 0
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES (convert(datetime,'2007-10-26 11:'+CAST(@.K AS VARCHAR)+':00'))
SET @.K=@.K+1
END|||I implemented the suggested modification, the parser says ok, but the run.time execution got the following error:

Server: Msg 242, Level 16, State 3, Line 12
The conversion of a char data to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated

The troubles keep going ...

Quote:

Originally Posted by sayedul

If you have the table like below:
TABLE1 ([DATETIME] DATETIME)

The modification your script to correct one as follows:

DECLARE @.K INT
SET @.K = 0
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES (convert(datetime,'2007-10-26 11:'+CAST(@.K AS VARCHAR)+':00'))
SET @.K=@.K+1
END

|||I already found my error! I wrote incorrect datetime format!
Now everything works!

Sorry, I am stupid ...

Thanks a lot anyway for the helpful suggetsions!!!

Stefano.