SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate Day of week from a date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

martinpalmer
Starting Member

United Kingdom
3 Posts

Posted - 07/17/2008 :  07:11:28  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 07/17/2008 :  07:31:28  Show Profile  Reply with Quote
Sorry.. i am a bit confused what are you trying to achieve here .


KH
Time is always against us

Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/17/2008 :  07:32:30  Show Profile  Reply with Quote
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.
Go to Top of Page

martinpalmer
Starting Member

United Kingdom
3 Posts

Posted - 07/17/2008 :  08:18:50  Show Profile  Reply with Quote
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!
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/17/2008 :  08:34:17  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/17/2008 :  08:39:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

martinpalmer
Starting Member

United Kingdom
3 Posts

Posted - 07/17/2008 :  08:41:22  Show Profile  Reply with Quote
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 :)
Go to Top of Page

ulfemsoy
Starting Member

2 Posts

Posted - 10/07/2011 :  12:21:38  Show Profile  Reply with Quote
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:
http://www.lazerwire.com/2011/10/sql-day-of-week.html

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000