tag:blogger.com,1999:blog-5822946.post115325176169805001..comments2014-12-09T13:49:06.600-06:00Comments on Marc's Musings: More on DATEs and SQLMarc Brookshttps://plus.google.com/103082173845303678192noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-5822946.post-13646760543026329382011-10-10T02:04:23.573-05:002011-10-10T02:04:23.573-05:00Regarding the &quot;Monday of any week&quot;, I ne...Regarding the &quot;Monday of any week&quot;, I needed to get the monday of any week, regardless of @@DATEFIRST settings. This the formula I came up with:<br /><b><br />DATEADD(DAY,-(DATEPART(weekday, GETDATE()) + @@DATEFIRST +5)%7, GETDATE())</b><br /><br />What it does is for instance given tuesday, and datefirst =7 (sunday)<br />3+7+5=15%7=1, and substracts 1, from the date, which gives the monday.<br /><br />For datefirst = 1(monday) it would be:<br />2+1+5=8%7=1 and substract 1, from the date, which gives mondayAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-19075021218748364652010-11-11T02:38:36.000-06:002010-11-11T02:38:36.000-06:00TheDate is meant to be where _YOU_ insert whatever...TheDate is meant to be where _YOU_ insert whatever date you want to manipulate... a column name, GETDATE(), etc...Marc Brooksnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-11203841366984369312010-11-11T02:02:31.000-06:002010-11-11T02:02:31.000-06:00TheDate is not working in sql?TheDate is not working in sql?Sannoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-42072575203995046892010-11-02T02:43:18.000-05:002010-11-02T02:43:18.000-05:00Thanks for the info !Thanks for the info !Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-49488860350131523382009-07-24T17:30:27.000-05:002009-07-24T17:30:27.000-05:00Step by step
First day of LAST month:
SELECT DA...Step by step<br />First day of LAST month:<br /> SELECT DATEADD(mm, DATEDIFF(mm, 0, GetDate()) - 1, 0)<br />First Monday of LAST month OR last Monday of prior month:<br /> SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) - 1, 0)), 0)<br /><br />So all we have to do is deal with when the prior Monday is in the wrong month (and add 7 days)<br /><br />DECLARE @today DATETIME<br />SET @today = &#39;2009-08-23 11:56:23&#39; --GetDate()<br />SELECT CASE WHEN RecentMonday &lt; StartOfLastMonth <br /> THEN <br /> DATEADD(wk, DATEDIFF(wk, 0, RecentMonday), 7) -- adjust up a week<br /> ELSE<br /> RecentMonday<br /> END AS FirstMondayOfTheMonth<br />FROM (<br />SELECT DATEADD(mm, DATEDIFF(mm, 0, @today) - 1 , 0) AS StartOfLastMonth<br />, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(mm, DATEDIFF(mm, 0, @today) - 1, 0)), 0) AS RecentMonday<br />) AS FakeIDIsposablenoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-18289474366251320992009-07-22T23:40:14.000-05:002009-07-22T23:40:14.000-05:00How about the first weekday of the last month? ;) ...How about the first weekday of the last month? ;) Anyone?Guestnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-65980379219158462742009-04-06T00:54:00.000-05:002009-04-06T00:54:00.000-05:00.999 is beyond the precision I stated. You cannot ....999 is beyond the precision I stated. You cannot use anything higher than .997 because the resolution of SQL Server is 3 milliseconds.IDisposablehttp://www.blogger.com/profile/02275315449689041289noreply@blogger.comtag:blogger.com,1999:blog-5822946.post-20086144936187589432009-04-05T22:55:00.000-05:002009-04-05T22:55:00.000-05:00If you use the suggested; select dateadd(dd, dated...If you use the suggested; <BR/>select dateadd(dd, datediff (dd, 0, '20080327 23:59:59.999'), 0)<BR/>It actually returns 20080328 00:00:00.000<BR/>It is obviously a rounding issue but is there a way around it?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-87423777352161634032009-02-17T09:15:00.000-06:002009-02-17T09:15:00.000-06:00You are a life saver :) many thanks.RockyYou are a life saver :) many thanks.<BR/><BR/>RockyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-3856875896878314362009-02-06T01:20:00.000-06:002009-02-06T01:20:00.000-06:00SQL Server (and all the others) store DateTime val...SQL Server (and all the others) store DateTime values in an internal binary representation that has NOTHING to do with how it is displayed for you when you make a query. What you need to do is specify the formatting you require in you client layer (where it belongs) or (at worst) in the SELECT statement. That said, your format is perfectly acceptable for an INSERT or UPDATE statement if single-quoted.IDisposablehttp://www.blogger.com/profile/02275315449689041289noreply@blogger.comtag:blogger.com,1999:blog-5822946.post-45328513488297696012009-02-06T00:47:00.000-06:002009-02-06T00:47:00.000-06:00Is it possible that I can store date/time in "date...Is it possible that I can store date/time in "datetime" column like this...<BR/><BR/>"2009-02-06 11:39:25"<BR/><BR/>Do I have to use something in "formula" field?<BR/><BR/>Please do help me!Waseem Sindhuhttp://www.blogger.com/profile/15703570835943740801noreply@blogger.comtag:blogger.com,1999:blog-5822946.post-82780694943505947782008-02-26T09:41:00.000-06:002008-02-26T09:41:00.000-06:00Sweeet tips on the dates...I've just used your adv...Sweeet tips on the dates...I've just used your advice for truncating the time. The customer wanted only to display the top of the hour.Mark-iester Brothahttp://www.blogger.com/profile/00940934754906803851noreply@blogger.comtag:blogger.com,1999:blog-5822946.post-87876970228719713182008-01-16T15:03:00.000-06:002008-01-16T15:03:00.000-06:00Why the bias against math on columns? For smaller ...Why the bias against math on columns? For smaller databases losing an index isn't an issue, and results in much simpler queries, just my humble opinion.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5822946.post-69914549133828794802007-05-03T15:33:00.000-05:002007-05-03T15:33:00.000-05:00Thank you! Very Helpful!!Thank you! Very Helpful!!Anonymousnoreply@blogger.com