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
Now everything works!
Sorry, I am stupid ...
Thanks a lot anyway for the helpful suggetsions!!!
Stefano.
