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
 datepart(dw) -> 1234567
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waterduck
Aged Yak Warrior

Malaysia
946 Posts

Posted - 05/16/2013 :  02:12:11  Show Profile  Reply with Quote
;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

India
52249 Posts

Posted - 05/16/2013 :  02:31:45  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/16/2013 :  02:32:16  Show Profile  Reply with Quote
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

Malaysia
946 Posts

Posted - 05/16/2013 :  02:34:01  Show Profile  Reply with Quote
exclude datefirst settings...which i forgot to mention...haha
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/16/2013 :  02:40:48  Show Profile  Reply with Quote
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

Malaysia
946 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/16/2013 :  04:00:24  Show Profile  Reply with Quote
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
  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.06 seconds. Powered By: Snitz Forums 2000