Dynamic date setting in loops

[arny64]

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.

[debasisdas]

What is the data that you are trying to insert. I dont think the initial condition of the loop satisfies at all.

[arny64]

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 ....

[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

[arny64]

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 ...

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

[arny64]

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.