problem with date function - 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

problem with date function

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-03 : 06:57:25

Dear Experts,

I'm trying to display the day of a perticular date.

I'm using the datename function like this

select datename(dw,'2007-05-13') then the output is correct....

if I didn't use the single quote, I'm not getting the correct answere.

and one more doubt.......

select datename(dw,01-06-2007) for this, I'm getting the correct answere.

but

select datename(dw,13-05-2007) is not giving the correct answere....

I dont know exactly what are the limitations.....

Please Guide me in this regard.

thank you very much

Vinod

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 07:07:27

declare @myDate datetime
select @myDate = 'yourdateHere'
select datename(dw, @myDate)

also look for "set dateformat" in BOL = books online = sql server help
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-03 : 07:13:05

you need to use single quote on the date. And when doing so, always use the ISO format YYYYMMDD to specify the date string.
Like '20070503'

When you are not using single quote, for example
select datename(dw, 2007-05-13) what SQL server is doing is calculate 2007 minus 5 minus 13 which gives you 1989. SQL Server date 0 is 1900 Jan 01. so 1989 days after 1900 Jan 01 will gives you 19050613

Run this and see the result


select datename(dw, 2007-05-13), convert(datetime, 2007-05-13)
select	2007-05-13, dateadd(day, 0, 1989)
select	datename(dw, '19050613')

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-03 : 07:24:01

Thank you Spirit and KH for the great guidance

Vinod

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 06:15:42

<<
And when doing so, always use the ISO format YYYYMMDD to specify the date string.
Like '20070503'
>>

+1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources