Define first date of a week - 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)

Define first date of a week

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-20 : 07:11:52

Eva writes "I have a table with the fiscal year (2004,2005) and the fiscal week(1,2,3,...) (fiscal year starts on dec 1). I need to include the date of the first day of every week. How can this be calculated?"

RM
Yak Posting Veteran

65 Posts

Posted - 2005-01-20 : 07:38:29

You can check a date with the fns DATEPART(dw,@date) which lets you know which day of the week it is. I think 2 is Monday by default

jones_d
Yak Posting Veteran

61 Posts

Posted - 2005-01-20 : 13:22:16

Hi,

Take a look at the following posting to get an understanding of what the first day of the week is.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44746

As mentioned in posting, it depends on the language setting of the user running the code.

For US, Sunday, day 7 is the first day of the week

For British English, Monday, day 1 is the first day of the week.

Assume you have an SP creating some sort of a time table.
Following code assumes monday is the first day of the week and returns the 17th Jan for each of the following:


select convert(datetime,'20050117') - (datepart(dw, convert(datetime,'20050117')) - @@datefirst)

select convert(datetime,'20050118') - (datepart(dw, convert(datetime,'20050118')) - @@datefirst)

select convert(datetime,'20050119') - (datepart(dw, convert(datetime,'20050119')) - @@datefirst)

select convert(datetime,'20050120') - (datepart(dw, convert(datetime,'20050120')) - @@datefirst)

select convert(datetime,'20050121') - (datepart(dw, convert(datetime,'20050121')) - @@datefirst)

select convert(datetime,'20050122') - (datepart(dw, convert(datetime,'20050122')) - @@datefirst)

select convert(datetime,'20050123') - (datepart(dw, convert(datetime,'20050123')) - @@datefirst)

select convert(datetime,'20050124') - (datepart(dw, convert(datetime,'20050124')) - @@datefirst)

if Sunday is the first day of your week, then you will need somethin like:

select convert(datetime,'20050124') - (@@datefirst - datepart(dw, convert(datetime,'20050124')))

Just need to substitute dates above with the your date variable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources