Casting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Casting

Author

Topic

new2sql101
Starting Member

4 Posts

Posted - 2011-05-02 : 07:23:42

Hi,

I have three fields TYear, TMonth, TDay. I would like to combine these fields to create a Date, But at the same time I needed it to return TWeek ( this should show the week of the year). I am very new to SQL, can this be done ? Please help.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-02 : 07:33:54

Assuming you Txxx are integers

Update YourTable
SET yourColumn = dateadd(day,Tday-1,dateadd(month,Tmonth-1,dateadd(year,Tyear-1900,0) ))

SELECT DatePart(week,YourCOlumn) from yourTable

Jim

Everyday I learn something that somebody else already knew

new2sql101
Starting Member

4 Posts

Posted - 2011-05-02 : 07:51:12

Would this be correct. Sorry for the stupid question..

SELECT VCargoAnalysis20052006.TMonth
, VCargoAnalysis20052006.TYear
, VCargoAnalysis20052006.TDay
, VCargoAnalysis20052006.StationId
, VCargoAnalysis20052006.AirportTo
, VCargoAnalysis20052006.OrigDest
, VCargoAnalysis20052006.FlightNo
, VCargoAnalysis20052006.TotalValue
, VCargoAnalysis20052006.TotalWeight
SET yourColumn = dateadd(day,Tday-1,dateadd(month,Tmonth-1,dateadd(year,Tyear-1900,0) ))
SELECT SET yourColumn = dateadd(day,Tday-1,dateadd(month,Tmonth-1,dateadd(year,Tyear-1900,0) ))
FROM airline.dbo.VCargoAnalysis20052006 VCargoAnalysis20052006

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 07:58:25

SELECT VCargoAnalysis20052006.TMonth
, VCargoAnalysis20052006.TYear
, VCargoAnalysis20052006.TDay
, VCargoAnalysis20052006.StationId
, VCargoAnalysis20052006.AirportTo
, VCargoAnalysis20052006.OrigDest
, VCargoAnalysis20052006.FlightNo
, VCargoAnalysis20052006.TotalValue
, VCargoAnalysis20052006.TotalWeight
, dateadd(day,Tday-1,dateadd(month,Tmonth-1,dateadd(year,Tyear-1900,0) )) AS YourDate
,DatePart(week,dateadd(day,Tday-1,dateadd(month,Tmonth-1,dateadd(year,Tyear-1900,0) ))) AS YourWeek
FROM airline.dbo.VCargoAnalysis20052006 VCargoAnalysis20052006
--------------------------
http://connectsql.blogspot.com/

new2sql101
Starting Member

4 Posts

Posted - 2011-05-02 : 08:01:16

ok thanks, how do i get it to return TWeek(week of year) ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 08:03:35

I already have added last column with name YourWeek, just rename it.

--------------------------
http://connectsql.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-02 : 08:04:05

Run the queries you were provided. The week part is in there.

Jim

Everyday I learn something that somebody else already knew