SQL Date question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Date question

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-01 : 10:02:01

Is it possible to code a historical date and calculate the day of week it was? I have a large complex report filled with dates and my client would like to see the day of week the specific dates relate back to. Not sure this is do-able or not.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 10:18:52

look at 'datepart' and 'set datefirst' in BOL

e.g.
select datepart(dw,getdate())

Em

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-01 : 10:22:49

elancaster -

I am still relatively new to sql. In my query (below) I am interested to pull the day of week from the Schedule Template Date (sr.ScheduleDate). Could you show me how you would do this? Thanks a million for your help! It will make my day!

/*Schedule Template Report*/
SET NOCOUNT ON

SELECT df.ListName AS Resource,
s.Description AS Template,
sr.ScheduleDate,
sr.Created,
sr.CreatedBy

FROM Schedule s
INNER JOIN DoctorFacility df ON s.DoctorResourceId = df.DoctorFacilityId
INNER JOIN ScheduleResource sr ON s.ScheduleId = sr.ScheduleId

WHERE --Filter on resource
(
(NULL IS NOT NULL AND df.DoctorFacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --- Filter on Schedule Template Date
(
sr.ScheduleDate >= ISNULL(NULL,'1/01/1900') AND
sr.ScheduleDate < dateadd(day,1,ISNULL(NULL,'1/01/3000'))
)
AND --- Filter on Created Date
(
sr.Created >= ISNULL('10/31/2007','1/01/1900') AND
sr.Created < dateadd(day,1,ISNULL('10/31/2007','1/01/3000'))

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 10:30:51

quote:

SELECT df.ListName AS Resource,
s.Description AS Template,
sr.ScheduleDate,
datepart(dw,sr.Scheduledate),
sr.Created,
sr.CreatedBy

that will return you a number for the 'day of the week'
the reason i said to look at 'set datefirst' as well is that this number is relative to the whatever you set the week to start on.
i.e.
if you said set datefirst 1
then 1 is monday, 2 is tuesday etc..
if you said set datefirst 7
then 1 is sunday, 2 is monday etc...

BOL explains it better than me

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-01 : 15:29:32

I would like to set 1 = Monday. How could I set datefirst to make this happen?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 15:55:15

just put it before your query ...

SET DATEFIRST 1

Em

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-01 : 16:11:36

This gives you a number independent of the setting of datefirst:


-- Mon=0, Tues=1, ... , Sat=5, Sun=6
select datediff(dd,0,MyDate)%7

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-01 : 16:48:20

In addition to the other methods listed there is also the DATENAME function:

SELECT DATENAME(DAY, CURRENT_TIMESTAMP)
SELECT DATENAME(DW, CURRENT_TIMESTAMP)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources