Dates question, DoW, WoM, MoY - 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

Dates question, DoW, WoM, MoY

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-09-04 : 01:21:24

I am using SQL Server 2005 on Windows Server 2003.

DoW = Day of Week, WoM = Week of Month, MoY = Month of Year!

I am looking to run reports or inform staff that their reports are due to be run today or this week.

I have a table (tbl_repsdue) with frequencies the reports are due to be run at (freqs), the report numbers (repno) and the person responsible for running that report (pers), the frequencies can be Daily, Weekly, Monthly, Quarterly.
They could be run 1st Day of Week, Wednesday or 2nd Week of Month.

I am looking to do something like this:-

1. Get all the reports that are due to be run today (or any other frequency I am looking for)
2. Inform the person responsible (via e-mail, through Aterts maybe?)

How from the frequencies above would I be able to find (based upon todays date) which reports are due?

I would somehow have to work out where today is in the calendar for DoW, WoM and MoY, any ideas please?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:46:15

for DoW use SELECT DATEPART(dw,GETDATE())
and MoY SELECT DATENAME(mm,GETDATE()) + DATENAME(yy,GETDATE())

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-09-04 : 02:10:22

Thanks for that,

another question,

How would i know what month of year it is based on the year starting 01/04/2008 and ending 31/03/2009 (using financial years instead of normal calendar years)

and then how would I know what week of month it is?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:15:27

The best thing to do in that case is to keep a table holding the financial year details. Otherwise you would require some manipulations to get you month corresponding to financial year and also the week.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-09-04 : 02:59:47

In Visual basic I would do it something like this:-

whichweek = DateDiff("ww", CDate("01/04/2008"), Now)

Is there nothing similar I could do is SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 03:03:54

quote:
Originally posted by Bill_C

In Visual basic I would do it something like this:-

whichweek = DateDiff("ww", CDate("01/04/2008"), Now)

Is there nothing similar I could do is SQL?

you can use same syntax in sql as well

SELECT DATEDIFF(wk,'01/04/2008',GETDATE())

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-09-04 : 03:21:00

Thanks, I didn't realise they were that comparable codewise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 03:32:06

quote:
Originally posted by Bill_C

Thanks, I didn't realise they were that comparable codewise.

you can use all datefunctions in sql also
like dateadd,datediff,datepart,...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources