InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:39:10 GMT20http://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]rramsey (7/29/2012)[/b][hr]One thing I noticed is this: I expected saturday to be returned as day 7 when sunday was day 1 however; the formula, Declare @ThisDay int = (DATEPART(weekday,@ThisDate) + @@DATEFIRST) % 7, always returns 0 for saturday. this query will show the results for each day of the week from 7/22/2012 to 7/28/2012.Select @@DATEFIRST as [DateFirst], ((DATEPART(weekday,'7/22/2012') + @@DATEFIRST) % 7) as SUN, ((DATEPART(weekday,'7/23/2012') + @@DATEFIRST) % 7) as MON , ((DATEPART(weekday,'7/24/2012') + @@DATEFIRST) % 7) as TUE, ((DATEPART(weekday,'7/25/2012') + @@DATEFIRST) % 7) as WED, ((DATEPART(weekday,'7/26/2012') + @@DATEFIRST) % 7) as THR, ((DATEPART(weekday,'7/27/2012') + @@DATEFIRST) % 7) as FRI, ((DATEPART(weekday,'7/28/2012') + @@DATEFIRST) % 7) as SATEverything works well as long as you expect saturday to be zero.[/quote]Yes Andy Tyan has explained how to handle it. Read two comments before this comment..he has explained in it.Sun, 29 Jul 2012 22:49:35 GMTDivya Agrawalhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxOne thing I noticed is this: I expected saturday to be returned as day 7 when sunday was day 1 however; the formula, Declare @ThisDay int = (DATEPART(weekday,@ThisDate) + @@DATEFIRST) % 7, always returns 0 for saturday. this query will show the results for each day of the week from 7/22/2012 to 7/28/2012.Select @@DATEFIRST as [DateFirst], ((DATEPART(weekday,'7/22/2012') + @@DATEFIRST) % 7) as SUN, ((DATEPART(weekday,'7/23/2012') + @@DATEFIRST) % 7) as MON , ((DATEPART(weekday,'7/24/2012') + @@DATEFIRST) % 7) as TUE, ((DATEPART(weekday,'7/25/2012') + @@DATEFIRST) % 7) as WED, ((DATEPART(weekday,'7/26/2012') + @@DATEFIRST) % 7) as THR, ((DATEPART(weekday,'7/27/2012') + @@DATEFIRST) % 7) as FRI, ((DATEPART(weekday,'7/28/2012') + @@DATEFIRST) % 7) as SATEverything works well as long as you expect saturday to be zero.Sun, 29 Jul 2012 08:37:52 GMTrramseyhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxNice article DivyaWed, 29 Jun 2011 00:18:34 GMTHardy21http://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThanks for saving me from writing it from scratch, Divya!@davidgr144David, I spotted your question too, and modified the query to always give me Mon=1 to Sun=7:[code="sql"]select 1+(datepart(dw, @date)+@@datefirst+5)%7[/code]Adding 5 before the MOD effectively cycles the days backwards, then adding 1 afterwards fixes the zero.Andy TThu, 28 Apr 2011 06:50:17 GMTAndy Tynanhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThanks John, I.Ronnoco & last but not least, Divya's website on date calculations in sql for your help in pointing me in the right direction ... for anyone with the same issue, the ssis expression did not require a condition, also the dateparts had to be all the same to return the previous days date no matter if the day had transitioned into another month.Corrected SSIS expression is shown below:"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR(DATEADD("dd",-1, GETDATE()) )+".csv"Finally thanks to Darren Green's article on SSIS date expressions at sqlis.com for his comparison of T-SQL Date functions and their SSIS equivalents.Thu, 23 Sep 2010 21:48:20 GMTdanajaohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThanks everyone for suggestions ... they have given me a couple of ideas as to how to proceed within BIDS SSIS on this issue ... will provide an update once it is resolved.Thu, 23 Sep 2010 18:54:36 GMTdanajaohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThanks John ... I'll try out as suggested and see how it goes ...Thu, 23 Sep 2010 18:46:26 GMTdanajaohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]danajao (9/7/2010)[/b][hr]Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.(DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"[/quote]Thanks for the appreciation of the article. Well, i have made a blog post about the date calculations. Please have a look at it, as i think it can be helpful to you for the problem you have..http://beyondrelational.com/blogs/divya/archive/2010/03/15/date-calculations-made-easy.aspxLet me know if it has still not resolved your problem.Wed, 08 Sep 2010 02:48:21 GMTDivya Agrawalhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]danajao (9/7/2010)[/b][hr]Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.(DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"[/quote]Sorry for the empty post..you can find the answer in the post below this..Wed, 08 Sep 2010 02:40:50 GMTDivya Agrawalhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxNot sure which versions of SQL Server this works in (but it works in 2005).declare @dt_date datetime;declare @dt_date_prev datetime;set @dt_date = convert(datetime,'2009-09-01');set @dt_date_prev = [b]@dt_date-1;[/b]Wed, 08 Sep 2010 02:02:07 GMTl.ronnocohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]danajao (9/7/2010)[/b][hr]Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.(DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( [b]DATEADD("mm", 0, GETDATE())[/b] ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "1"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"[/quote]It looks from the code that you may be trying to code an expression for SSIS. If true, you may get more help in the SSIS forum. But meanwhile, at the core of it, finding a previous date can be trivial with the DATEADD function:[code="sql"]select DATEADD(dd,-1,'20100901')[/code]returns 8/31/2010You want to build the filename from pieces of that common result. Your problem may be that DATEADD("mm",0,GETDATE()) which I highlighted in my quote of your post above. It will alway give you the same month that you start with. Try making it DATEADD("mm",-1,GETDATE()) and let us know if that solves things for you.Tue, 07 Sep 2010 18:22:31 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.(DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"Tue, 07 Sep 2010 17:39:13 GMTdanajaohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxHi - thanksThis is a great solution. I made a function that can handle this:cool:Fri, 05 Mar 2010 02:22:04 GMTbbothmahttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]jamesburkill (2/23/2010)[/b][hr]Ok. You've won me over.I just did A quick test on the efficiency of each method.Using the script below, my method took 2:20 to 2:30 to execute. Yours consistently took 1:20, though I did have to run it 100 million times to get those numbers.Declare @i intDeclare @day intset @i=0While @i<=100000000Begin Set @Day=((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1 set @i=@i+1EndDeclare @i intDeclare @day intset @i=0While @i<=100000000Begin Set @Day=1 + (DATEDIFF(DAY, 0, '2009-12-17')+700000) % 7 set @i=@i+1End[/quote]Well, thanks @jamesburkillDatediff will take time as compared to getting datepart..Tue, 23 Feb 2010 04:13:21 GMTDivya Agrawalhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxOk. You've won me over.I just did A quick test on the efficiency of each method.Using the script below, my method took 2:20 to 2:30 to execute. Yours consistently took 1:20, though I did have to run it 100 million times to get those numbers.Declare @i intDeclare @day intset @i=0While @i<=100000000Begin Set @Day=((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1 set @i=@i+1EndDeclare @i intDeclare @day intset @i=0While @i<=100000000Begin Set @Day=1 + (DATEDIFF(DAY, 0, '2009-12-17')+700000) % 7 set @i=@i+1EndTue, 23 Feb 2010 02:23:04 GMTjamesburkillhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]l.ronnoco (2/20/2010)[/b][hr] SELECT 1 + (DATEDIFF(DAY, 0, @target_date)+700000) % 7The 700000 can be any number divisible by 7. This will work all the way back to before the year 0 so should be fine for any non-biblical applications :-)[/quote]This probably does not work with SQL 2008 (I don't have that version available here), but it certainly does not on SQL 2005. Those still on earlier versions will have to remember that [b]datetime[/b] only supports values since 1753, so you can't even set target_date to an earlier value. Even if @target_date can be set to something earlier in SQL 2008, the eleven day adjustment in 1752 would mess up the modulo 7 algorithm.(Why was there no September 3-13 in 1752? here's an explanation: [url]http://www.ancestry.com/learn/library/article.aspx?article=3358[/url] )Mon, 22 Feb 2010 10:02:53 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThe method I posted earlier works fine for pre-1900 dates and gives an answer where mon=1 and sun=7Select ((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1Others have posted various ways of deriving the name of the day of week. I don't know why you wouldn't just do this...Select DateName(dw,'2009-12-17')If you only want the first 3 letters then...Select Left(DateName(dw,'2009-12-17'),3)Sun, 21 Feb 2010 04:26:02 GMTjamesburkillhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxI think Lynn's solution is best using SELECT 1 + DATEDIFF(DAY, 0, @target_date) % 7but it will fail for dates prior to 1/1/1900 - not a problem in most scenariosbut can be fixed withSELECT 1 + (DATEDIFF(DAY, 0, @target_date)+700000) % 7The 700000 can be any number divisible by 7. This will work all the way back to before the year 0 so should be fine for any non-biblical applications :-)I'd thought of:SELECT (convert(int,convert(float,@target_date))+70000)%7+1But the DATEDIFF solution is much more elegant and probably executes faster. CONVERTing direct to an INT would be make it much better but SQL likes to round UP if the datetime is past midday (pain).However if you're only dealing with dates as at midnight then this works:SELECT (convert(int,@target_date)+70000)%7+1And if you're only dealing with midnight dates and you're not bothered about dealing with pre 1900 dates then this works:SELECT convert(int,@target_date)%7+1The shortest solution thus far methinks :w00t:Sat, 20 Feb 2010 04:25:35 GMTl.ronnocohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxIt's interesting how in this discussion we have all been very busy trying to map numbers to the days of the week to 'make sense out of them' and to 'get a handle on them'. Whenever I have to deal with days of the week I make a concerted effort to stay away from that. And it's surprising how many problems you can solve that involve days of the week without ever involving the concept of Monday=1, Tuesday=2, etc.Fri, 19 Feb 2010 14:18:56 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxAnother solution is to use date math. [code="sql"]DECLARE @dt DATETIME, @Day_Start SMALLINT SET @Day_Start = 0--0=Sun,1=Mon,2=Tue,3=Wed,4=Thu,5=Fri,6=SatSET @dt = '20091219'SELECT --Day Of Week formula: --datediff of @Day_Start (this is the start day, for the week of @dt) and @dt + 1 day. DATEDIFF( DAY, CASE WHEN @dt >= DATEADD(DAY,@Day_Start,DATEADD(WEEK,DATEDIFF(WEEK,0,@dt),0)) THEN DATEADD(DAY,@Day_Start,DATEADD(WEEK,DATEDIFF(WEEK,0,@dt),0)) --Get the start of this week & add @Day_Start ELSE DATEADD(DAY,@Day_Start,DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,@dt),0)))--Get the start of last week & add @Day_Start END, DATEADD(DAY,1,@dt) --Add a day to get the correct count )[/code]Fri, 19 Feb 2010 11:37:24 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxNice Article.ThanksFri, 19 Feb 2010 11:12:42 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]Divya Agrawal (2/19/2010)[/b][hr]May i know ur datefirst setting?Just try running "SELECT @@DATEFIRST"This will give which day is considered as the first day of the week..And according to that the script has to be changed..I have considered @@DATEFIRST =7 i.e. Sunday as the first day of the week. So based on that for Thursday it is returning 5.[/quote]Yes my datefirst was set to 7, but according to the article the trick removed the dependence on datefirst setting.honestly, I had been looking for this function off and on, but now I think I will use the far more simpler form of:DECLARE @lc_DEBUG VARCHAR(5)DECLARE @lc_todayIs CHAR(3)SET @lc_DEBUG = 'TRUE'-- Even simpler way presuming we stay in English -- Thanks to SQL Server Central .com website.SELECT @lc_todayIs =UPPER(SUBSTRING(DATENAME(weekday, GETDATE()), 1,3))IF @lc_DEBUG = 'TRUE' SELECT '@todayIs Set to: ' AS Info, @lc_todayIs AS ValSince we only deal with english as the default language. The above gives me the three letter abbreviations of the day of the week which lets me query by what day it is.Fri, 19 Feb 2010 09:25:21 GMTBrian Munierhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]SwePeso (2/19/2010)[/b][hr]SELECT 1 + DATEDIFF(DAY, 0, '20091217') % 7[/quote]This works because 1 January 1900 was a Monday. I knew that tidbit (and used it in a comment on Lynn's article on common datetimes), so should have recognized it could be a more diirect way to calcuate a numeric day-of-week. Simpler code to look at and presumably more efficient than involving @@DATEFIRST.[url=http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx][b]"Some Common Date Routines"[/b][/url] by Lynn Pettis.Fri, 19 Feb 2010 09:06:59 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxYou are correct that a string literal date with separators is not multi-language compatible, but it is DATEFORMAT independent. My example should be revised to use unseparated 'CCYYMMDD' format, which is listed by the MSDN link as a valid ISO 8601 format that is multilanguage compatible and DATEFORMAT independent. I would prefer not specifying a time portion because we are just looking for a day name, but I guess that adding some kind of valid time string as you described would be fine. It is unclear to me based on the MSDN article whether the ISO unseparated numeric date format is an international standard. If so, I would prefer going with that when the time is irrevelant.Scott VFri, 19 Feb 2010 07:23:16 GMTScott Vhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxHi Scott,(I have corrected my first posting here:) yes you are right with your code, because DATENAME expects a date. But in the other cases where dateadd is used the style 'CCYY-MM-DD' is not working. ISO 8601 means a complete date + time for datetime, e. g.2004-05-23T14:25:10See: [url]http://msdn.microsoft.com/en-us/library/ms180878.aspx#ISO8601Format[/url]So the often used format 2004-05-23 14:25:10 (without the T in the middle) is the unstructured SQL Server format. This is not language independent, whereas 20040523 14:25:10 would work in germany, too. set language germanDECLARE @d as datetimeset @d = '2004-05-23 14:25:10 'select @dgoDECLARE @d as datetimeset @d = '20040523 14:25:10 'select @d'2004-05-23' works only with date, because ISO 8601 defines this format for a date without a time-part.Fri, 19 Feb 2010 06:59:40 GMTChristoph Muthmannhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxThe code as I submitted it works correctly, including the date string literal. The format for the date string literal argument to the function is ISO 8601 ANSI SQL ('CCYY-MM-DD'), this is independent of your date format system settings. You can read about it here: [url=http://msdn.microsoft.com/en-us/library/ms180878.aspx]http://msdn.microsoft.com/en-us/library/ms180878.aspx[/url]I misunderstood the intention of the article and thought the intention was to get a reliable day name string, not a day number. The example that I gave was intended to give a translated day name; that's why I suggested this way of doing it.Scott VFri, 19 Feb 2010 06:48:47 GMTScott Vhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]Carlo Romagnano (2/19/2010)[/b][hr]The main problem is not the format of date (dmy or mdy or ymd) nor if monday is the first day of the week, but identify the day '2010-2-1' as monday.DECLARE @d as datetimeset @d = '2010-2-1'SELECT cast(@d as int) % 7 + 1 This identifies Monday as 1.[/quote]DECLARE @d as datetimeset @d = '2010-2-1 19:00'SELECT cast(@d as int) % 7 + 1 It reports 2 for me, due to integer translation.Fri, 19 Feb 2010 06:42:55 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxSwePeso's solution works for me. It is independent from language and @@datefirst.Good Point Peter!:-PFri, 19 Feb 2010 06:22:46 GMTChristoph Muthmannhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]svansickle (2/19/2010)[/b][hr]SELECT DATENAME(weekday, 'CCYY-MM-DD') does it for me. I would hope that this builtin function handles different DATEFIRST settings correctly.[/quote]Don't forget language issues. Where I am, when I doSELECT DATENAME(weekday, GETDATE())I getvenerdiAnd, oh yes, '2010-02-19' will not work. You need to code '19-02-2010'. Now, that's a strange one.Fri, 19 Feb 2010 06:20:15 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]Brian Munier (2/19/2010)[/b][hr][quote][b]davidgr144 (2/19/2010)[/b][hr]This is a great tip. Thanks.Can I raise a question regarding it though?Maybe I'm misreading but if the date you pick is Saturday (instead of Thursday in the example - 17th Dec 2009 was a Thursday I believe), unless I'm very much mistaken by the values returned from modulo, the final result after applying the modulo will be zero. Seven needs to be added in this case.[/quote]Yep, I just tried that withSELECT (DATEPART(dw, '2010-02-20') + @@DATEFIRST) % 7 AS dwand the result is 0, I have just updated my case statement to use 0 to 6 as the numbers.Theoretically you could add 1 to the results, but then SAT would be 1 and Friday 7.Simple code to convert to a 3 letter day of the week:DECLARE @lc_DEBUG VARCHAR(5)DECLARE @lc_todayIs CHAR(3)SET @lc_DEBUG = 'TRUE'SELECT @lc_todayIs =CASE (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7 -- Thanks to SQL Server Central .com website. WHEN 2 THEN 'MON' WHEN 3 THEN 'TUE' WHEN 4 THEN 'WED' WHEN 5 THEN 'THU' WHEN 6 THEN 'FRI' WHEN 0 THEN 'SAT' WHEN 1 THEN 'SUN' ELSE 'ERR'ENDIF @lc_DEBUG = 'TRUE' SELECT '@todayIs Set to: ' AS Info, @lc_todayIs AS ValBEM - Fixed to use GETDATE()BEM 2 - Fixed GETDATE() into the correct spot.BEM 3 - Edited my example to a Saturday date. Argh[/quote]May i know ur datefirst setting?Just try running "SELECT @@DATEFIRST"This will give which day is considered as the first day of the week..And according to that the script has to be changed..I have considered @@DATEFIRST =7 i.e. Sunday as the first day of the week. So based on that for Thursday it is returning 5.Fri, 19 Feb 2010 06:17:34 GMTDivya Agrawalhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxSELECT DATENAME(weekday, 'CCYY-MM-DD') does it for me. I would hope that this builtin function handles different DATEFIRST settings correctly.Fri, 19 Feb 2010 05:58:24 GMTScott Vhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxGood idea ,we are eager to learn from u .Fri, 19 Feb 2010 05:12:26 GMTnirmallyakoley-941182http://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]davidgr144 (2/19/2010)[/b][hr]This is a great tip. Thanks.Can I raise a question regarding it though?Maybe I'm misreading but if the date you pick is Saturday (instead of Thursday in the example - 17th Dec 2009 was a Thursday I believe), unless I'm very much mistaken by the values returned from modulo, the final result after applying the modulo will be zero. Seven needs to be added in this case.[/quote]Yep, I just tried that withSELECT (DATEPART(dw, '2010-02-20') + @@DATEFIRST) % 7 AS dwand the result is 0, I have just updated my case statement to use 0 to 6 as the numbers.Theoretically you could add 1 to the results, but then SAT would be 1 and Friday 7.Simple code to convert to a 3 letter day of the week:DECLARE @lc_DEBUG VARCHAR(5)DECLARE @lc_todayIs CHAR(3)SET @lc_DEBUG = 'TRUE'SELECT @lc_todayIs =CASE (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7 -- Thanks to SQL Server Central .com website. WHEN 2 THEN 'MON' WHEN 3 THEN 'TUE' WHEN 4 THEN 'WED' WHEN 5 THEN 'THU' WHEN 6 THEN 'FRI' WHEN 0 THEN 'SAT' WHEN 1 THEN 'SUN' ELSE 'ERR'ENDIF @lc_DEBUG = 'TRUE' SELECT '@todayIs Set to: ' AS Info, @lc_todayIs AS ValBEM - Fixed to use GETDATE()BEM 2 - Fixed GETDATE() into the correct spot.BEM 3 - Edited my example to a Saturday date. ArghFri, 19 Feb 2010 04:58:20 GMTBrian Munierhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]Jan v.d. Kruyk (2/19/2010)[/b][hr]It disables @@DATEFIRST and it's only usable if Sunday is firstday. So not usable for me.[/quote]You have to make a bit change in the script, as per the first day of your week. If you have monday as the first day then just change the code to [code="sql"]SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST-1) % 7 AS dw[/code]This has been conveyed in the discussion.Let me know if you any further queries :)Fri, 19 Feb 2010 04:55:21 GMTDivya Agrawalhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxIt disables @@DATEFIRST and it's only usable if Sunday is firstday. So not usable for me.Fri, 19 Feb 2010 04:40:20 GMTJan v.d. Kruykhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote][b]Kelsey Thornton (2/19/2010)[/b][hr]This article refers to the "English" calendar.Of course, it should refer to the "American" calendar.In England, like in most of the rest of Europe, the week is deemed to start on Monday, not Sunday. Thursday is, therefore, the fourth day of the week, not the fifth.<snip>Similarly, the date format is different.Today in America is 02/19/2010, in England it's 19/02/2010.As a computer geek I actually prefer the Japanese method of writing dates (like 2010/02/19), as this can very easily be used as a (part of a) file name, eg SQLBackup_20100219.BAK - Now if you have many files in a single directory/folder you can sort by name and they're nicely in date order too :-)[/quote]I agree with everything you say :-Dyour "japanese" method of writing dates - thats actually the international standardhttp://en.wikipedia.org/wiki/ISO_8601Fri, 19 Feb 2010 03:50:36 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxi like this solution "((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1"this is the bestFri, 19 Feb 2010 03:18:13 GMTkhayot.khalimovhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxTo get Monday=1 and Sunday=7 I've been using this for years...Select ((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1Fri, 19 Feb 2010 03:16:42 GMTjamesburkillhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspxWe have US and UK offices, and often encountered this problem with one user getting one result and another a different one for the same "DATEPART(dw" statement (as the DATEFIRST is user dependent based on language rather than overall on the server).We have many developers and trying to get them all to adopt a new way of returning the day of the week would be impossible, and chances are you would forget once in a while, or a new member of staff would simply use the original code anyway.Instead of that we encourage users to always explicitly specify SET DATEFIRST in their code, and we run a check each night for any procs that use "DATEPART(dw" without a DATEFIRST in them, with this:EXEC master.dbo.sp_MSForEachDB 'USE ?SELECT DB_NAME() AS "Database", xtype, name FROM(SELECT DISTINCT so.id, so.name, xtype from syscomments sc WITH (NOLOCK) JOIN sysobjects so WITH (NOLOCK) on sc.id = so.idWHERE text like ''%datepart%dw%''AND not exists (SELECT * from syscomments s WITH (NOLOCK) where text like ''%DATEFIRST%'' AND s.id = sc.id) ) A'It's fairly basic, but it can help catch issues.Fri, 19 Feb 2010 03:07:36 GMTGrumpyDBAhttp://www.sqlservercentral.com/Forums/Topic868760-1418-1.aspx[quote]Our goal is to get the day of the week for the date '2009-12-17' as '5' regardless of the DateFirst setting.[/quote]SET DATEFIRST lets you define, when you want your week to start.DATEPART (dw, <date>) gets you the "day of the week" for <date>, based upon the DATEFIRST definition we just did.NOW:"Our goal is to get the day of the week for the date '2009-12-17' as '5' regardless of the DateFirst setting." sounds quite ... strange:2009-12-17 was a Thursday.You start your week on Sunday or Monday or Friday: 2009-12-17 was a Thursday!Now: If you start your week on Monday, Thursday is the 4th day of the week, not the 5th.Why should you make it the 5th, when it is the 4th?Fri, 19 Feb 2010 03:04:34 GMTsidhi