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.

Dynamic Crystal Report

Dear All,
How can I populate a data report using a Sqlquery.
Thanks in Advance
DanaCreate a stored procedure with that query and design the report using that procedure

dynamic column population?

Hello I'm new to reporting services (2000)
How do I dynamically populate a matrix column (or table) using the
parameters entered by users?
Example: A user enters a date range to filter the dataset. Parameters are
Start Date and End Date.
Then I want to be able to display in the report all the days between start
date and end date in the columns so that the
Table headers contain all the days/dates between start date and end date.
Start: 1/1/2006
End: 1/7/2006
--Col1: 1/1--Col2: 1/2--Col3: 1/3--Col4:
1/4--Col5:1/5--Col6: 1/6--Col7: 1/7
Row1
Row2
Row3
..how is this implemented in reporting services? is this possible?hey man,
right click on the 1st column header and select [expression] then you will
have to type something like:
=Parameters!startdate.Value
for the other columns you have to add 1 or 2 or 3 to the [days] part of the
parameter... then for the last column right click and select [expression]
then type:
=Parameters!enddate.Value
peace out...
"Abi" wrote:
> Hello I'm new to reporting services (2000)
> How do I dynamically populate a matrix column (or table) using the
> parameters entered by users?
> Example: A user enters a date range to filter the dataset. Parameters are
> Start Date and End Date.
> Then I want to be able to display in the report all the days between start
> date and end date in the columns so that the
> Table headers contain all the days/dates between start date and end date.
> Start: 1/1/2006
> End: 1/7/2006
>
> --Col1: 1/1--Col2: 1/2--Col3: 1/3--Col4:
> 1/4--Col5:1/5--Col6: 1/6--Col7: 1/7
> Row1
> Row2
> Row3
> ..how is this implemented in reporting services? is this possible?
>
>