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
 datepart(dw) -> 1234567

Author  Topic 

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-16 : 02:12:11
;with cte (d) as (
select getdate()
union all
select 1 + d
from cte
where d < getdate() + 10
)
select d, datename(dw, d), (cast(cast(d as float) as int) % 7) + 1
from cte

to get
1 : monday
2 : tuesday
3 : wednesday
4 : thursday
5 : friday
6 : saturday
7 : sunday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 02:31:45
again actual datepart value for day of week depends on your DATEFIRST settings

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 02:32:16
again actual datepart value for day of week depends on your DATEFIRST settings

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-16 : 02:34:01
exclude datefirst settings...which i forgot to mention...haha
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 02:40:48
quote:
Originally posted by waterduck

exclude datefirst settings...which i forgot to mention...haha


yep
And also returning a value like this can confuse users as well if they didnt understand that this is a customised day of week value so the documentation has to be precise and clear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-16 : 02:53:04
any implicit conversion can be done to reduce the code length? =P
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 04:00:24
i would have just done

DATEDIFF(dd,0,datefield)% 7 + 1

instead of all those convertions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -