Calculate Day of week from a date

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

Calculate Day of week from a date

Author

Topic

martinpalmer
Starting Member

3 Posts

Posted - 2008-07-17 : 07:11:28

Hi guys, newbie here :)

ok, i've been asked to try and find out a solution for the following, i've sort of figured some of it out but its getting towards the end of the week and my brain is slowly grinding to a halt....

I have a list of dates that i need to convert to day of the week, in the format of dd/mm/yyyy

I have managed to find the following command and it works:

SELECT DATENAME(WEEKDAY,GETDATE()) AS 'DAY NAME'

If i change the "getname" to a date or the field name in the table where my dates are it will work to the mm/dd/yyyy syntax. ok, this is where i'm feeling silly, trying to put the dateconvert function in with this one to pull through:

Query:
SELECT DATENAME(WEEKDAY,"17/07/2008") AS 'DAY NAME'

Result:
DAY NAME
Thursday

HELP?!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 07:31:28

Sorry.. i am a bit confused what are you trying to achieve here .

KH
[spoiler]Time is always against us[/spoiler]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-17 : 07:32:30

What's the datatype of the column where your dates are? You really want it to be datetime, but it appears as though it might be varchar...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

martinpalmer
Starting Member

3 Posts

Posted - 2008-07-17 : 08:18:50

panic over.....managed to get hold of a sql guru mate of mine at old place where i used to work.

SELECT datename(weekday,substring('17/07/2008',7,4) + substring('17/07/2008',4,2) + substring('17/07/2008',1,2)) AS WEEKDAY

sorry if confused anybody on my first posting - told you i was tired!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-17 : 08:34:17

[code]set dateformat dmy
SELECT datename(weekday, '17/07/2008') AS WEEKDAY[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 08:39:27

quote:
Originally posted by martinpalmer

panic over.....managed to get hold of a sql guru mate of mine at old place where i used to work.

SELECT datename(weekday,substring('17/07/2008',7,4) + substring('17/07/2008',4,2) + substring('17/07/2008',1,2)) AS WEEKDAY

sorry if confused anybody on my first posting - told you i was tired!

Always express date values in YYYYMMDD format which would work for all adte settings

Madhivanan

Failing to plan is Planning to fail

martinpalmer
Starting Member

3 Posts

Posted - 2008-07-17 : 08:41:22

quote:
Originally posted by RyanRandall

set dateformat dmy
SELECT datename(weekday, '17/07/2008') AS WEEKDAY

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

little bit easier, cheers :)

ulfemsoy
Starting Member

2 Posts

Posted - 2011-10-07 : 12:21:38

To get a deterministic value for the day of week for a given date you could use a combination of datepart and @@firstday. Otherwise you're dependent on the settings on the server.

Check out the following site for a better solution:
[url]http://www.lazerwire.com/2011/10/sql-day-of-week.html[/url]

The day of week will then be in the range 0 to 6, where 0 is Sunday, 1 is monday, etc. Then you can use a simple case statement to return the correct weekday name. This way your're not dependent on the language settings on the server.

lazerwire.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources