Calculating weekday of a date - 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

Calculating weekday of a date

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-25 : 04:46:16

Hi All

I need some help
Aim – To work out what day of the week a Closedate Falls on
Date format is of Closedate is “Year-month-Day” (varchar(50)
For eg
2013-11-25
2013-11-21
2013-11-19
2013-11-18
Desired results
2013-11-25 Monday
2013-11-21 Thursday
2013-11-19 Tuesday
2013-11-18 Monday

Looking forward to your help

Regards
D

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-25 : 04:52:12

I have Created the following query(See below) but i am recieving the following error message

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

select

DATEPART(weekday, 'CloseDate')AS WEEKDAY
from #build
where [StageName] in ('Unable to Approve', 'Opportunity Lost')
and year([CloseDate]) = '2013'
and [Channel Indicator] = 'Field'
and MONTH([CloseDate]) = '10'
order by CloseDate desc

Bu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 05:07:18

it should be


select 

DATEPART(weekday, [CloseDate])AS WEEKDAY
from #build
where [StageName] in ('Unable to Approve', 'Opportunity Lost')
and year([CloseDate]) = '2013'
and [Channel Indicator] = 'Field'
and MONTH([CloseDate]) = '10'
order by CloseDate desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 05:29:07

quote:
Originally posted by masond

HI Visakh16

Currently your soultions provides me with 1-7, is there anyway that i can state that 1 = monday, 2 = tuesday etc ?
Also is there a function which enables me to find out what week number that is ?

for eg closedate = '31/10/2013' is weeknum 44


SELECT
DATENAME(weekday, [CloseDate])AS WEEKDAY,
DATEPART(wk,[CloseDate])AS WEEKNO
from #build
where [StageName] in ('Unable to Approve', 'Opportunity Lost')
and year([CloseDate]) = '2013'
and [Channel Indicator] = 'Field'
and MONTH([CloseDate]) = '10'
order by CloseDate desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources