DATENAME fucnction Behaviour - 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 2008 Forums

Transact-SQL (2008)

DATENAME fucnction Behaviour

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-12-28 : 20:31:02

Hi All,

while Executing below query :

SELECT DATENAME(DW,'2012-01-01') union
SELECT DATENAME(DW,'2012-01-02')union
SELECT DATENAME(DW,'2012-01-03')union
SELECT DATENAME(DW,'2012-01-04')union
SELECT DATENAME(DW,'2012-01-05')union
SELECT DATENAME(DW,'2012-01-06')union
SELECT DATENAME(DW,'2012-01-07')

It is generating following resulset

Friday
Monday
Saturday
Sunday
Thursday
Tuesday
Wednesday

Which is incorrect .
But if i execute select statement one by one and look for result it gives me correct weekday .Why DATENAME function behaves inconsistence.

Suggestions are welcome..

Thanks
vijay Sahu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-28 : 20:38:23

it is actually correct. It is just the ordering of the record is not as what you think.
Do note that without ORDER BY, the sequence of the record return might be random.

Try


SELECT '2012-01-01' , DATENAME(DW,'2012-01-01') union
SELECT '2012-01-02' , DATENAME(DW,'2012-01-02') union
SELECT '2012-01-03' , DATENAME(DW,'2012-01-03') union
SELECT '2012-01-04' , DATENAME(DW,'2012-01-04') union
SELECT '2012-01-05' , DATENAME(DW,'2012-01-05') union
SELECT '2012-01-06' , DATENAME(DW,'2012-01-06') union
SELECT '2012-01-07' , DATENAME(DW,'2012-01-07')

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources