A simple Table 2

[starbase59]

I now have a simple table that has only the calendar dates for 2003(I was helped earlier at this site). I want to add the corresponding DaysOfTheWeek column for each date. Can I (and how do I) utilize the same loop? See code below:

Declare @year int, @dt smalldatetime
Set @year = 2003
Set @dt = Cast ( @year AS char(4)) + '-01-01'

While Year(@dt) = @year
Begin
Insert tblDates Values(@dt)
Set @dt = @dt + 1
End

 

[ClaireHCM]

but what you queried can only have one result

"2004-01-01 00:00:00"

Is this what you want?

What does that mean by "the corresponding DaysOfTheWeek column for each date"?
"2004-01-01 00:00:00" doesnt look like the result you want.

 

[cmgaviao]

I assume you have this:

Dates
1/1/2001
1/2/2001

and you want this:

dates dayofweek
1/1/2001 4 (or maybe you want wednesday)
1/2/2002 5 thursday?

in which case, you've got the table, just add the appropriate dayofweek field

int or varchar

and run an update query

update dates
set dayofweek = {fn dayname(cast(dates as datetime))}

or set dayofweek = {fn dayofweek(cast(dates as datetime))}

I know there's sqlserver native functions for this, but as I have to use this in other dbms this seems to work fine...I don't know what the performance hit is for using ODBC, but maybe someone can enlighten us. This is of course if this is what you were looking to do

 

[cmgaviao]

ack, I put in those stupid casts...you dont need that it should be:

update dates
set dayofweek = {fn dayname(dates)}

or set dayofweek = {fn dayofweek(dates)}

 

[sunila7]

Hi,

Is this wht u r looking for

Declare @year int, @dt smalldatetime
Set @year=2003
Set @dt = Cast(@year As char(4)) + '-01-01'

While Year(@dt) = @year
Begin
Insert dateTable(dt, dayoftheweek) Values(@dt,datename(dw,@dt))
Set @dt = @dt + 1
End


Sunil