July 10, 2009 at 2:31 pm
Hi guys, i was looking for a line that gives me the date of the sunday of the week of a given date
and i found this.
dateadd(day,datediff(day,'19000107',@ITEMDATE)/7*7,'19000107')
It works. but i just dont know what that constant '19000107' means.
I know is the startdate of the datediff function and that it can be time, date, smalldatetime, datetime, datetime2 o datetimeoffset
maybe it can be '1900/01/07' but WHY 01/07? i really just dont get the sense on that number :S
What does that '19000107' means ? :crazy:
July 10, 2009 at 2:41 pm
I guess that was a sunday.
July 10, 2009 at 3:12 pm
19000107 is Sunday, Jan 7, 1900.
The code finds the number of day since 1900-01-07, divides by 7 to get the number of weeks since than, and adds that number of weeks to 1900-01-07 to give you the Sunday on or before @ITEMDATE.
The technique is similar to the way I do it in the function on the link below, except that I start with much earlier dates, 1753-01-01 thru 1753-01-07, because the code you posted would give an error for days before 1900-01-07.
Start of Week Function
July 10, 2009 at 3:21 pm
Michael Valentine Jones (7/10/2009)
19000107 is Sunday, Jan 7, 1900.The code finds the number of day since 1900-01-07, divides by 7 to get the number of weeks since than, and adds that number of weeks to 1900-01-07 to give you the Sunday on or before @ITEMDATE.
The technique is similar to the way I do it in the function on the link below, except that I start with much earlier dates, 1753-01-01 thru 1753-01-07, because the code you posted would give an error for days before 1900-01-07.
Start of Week Function
It would give a result. The calculation at hand actually yields the first sunday AFTER the itemdate for anything prior to 1/1/1900, and the last sunday BEFORE itemdate after 1/1/1900. But it does return a result.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2009 at 3:25 pm
Oh then that was the trick, thanks alot for the explanation :w00t:
i also checked the link of your post and it was helpful too, now im curious about for what u need dates from 1753 or so?, history or something? just wondering.
July 10, 2009 at 3:37 pm
zenrigar (7/10/2009)
Oh then that was the trick, thanks alot for the explanation :w00t:i also checked the link of your post and it was helpful too, now im curious about for what u need dates from 1753 or so?, history or something? just wondering.
As I recall - 1753 is the date you end up with working your way back from 12/31/9999. In other words, someone decided that 12/31/9999 was going to be the end date, and based on the scale and presicion they came up with - 1753 ended up being the earliest they could go.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2009 at 3:47 pm
1753-01-01 00:00:00.000 is the earliest possible SQL Server datetime value.
I believe that the reason 1753 is used is because that was the first full year that English speaking countries used the newer Gregorian calendar, instead of the older Julian calendar.
July 10, 2009 at 3:48 pm
zenrigar (7/10/2009)
Oh then that was the trick, thanks alot for the explanation :w00t:i also checked the link of your post and it was helpful too, now im curious about for what u need dates from 1753 or so?, history or something? just wondering.
I think it actually has more to do with our current calendar.
July 10, 2009 at 3:51 pm
Matt Miller (7/10/2009)
Michael Valentine Jones (7/10/2009)
19000107 is Sunday, Jan 7, 1900.The code finds the number of day since 1900-01-07, divides by 7 to get the number of weeks since than, and adds that number of weeks to 1900-01-07 to give you the Sunday on or before @ITEMDATE.
The technique is similar to the way I do it in the function on the link below, except that I start with much earlier dates, 1753-01-01 thru 1753-01-07, because the code you posted would give an error for days before 1900-01-07.
Start of Week Function
It would give a result. The calculation at hand actually yields the first sunday AFTER the itemdate for anything prior to 1/1/1900, and the last sunday BEFORE itemdate after 1/1/1900. But it does return a result.
By error, I meant incorrect results. 🙂
Really the worst kind, because if you don't look carefully, they look plausible.
July 12, 2009 at 12:54 am
The "trick" is the INTEGER division made up by "/ 7 * 7"
The first division divides by seven and discards the fractional part.
Then the integer part is multiplied by seven.
N 56°04'39.16"
E 12°55'05.25"
July 13, 2009 at 10:36 am
>someone decided that 12/31/9999 was going to be the end date
Oh, just GREAT.
That means when Y10K rolls around, we're gonna hafta listen to the Mac camp brag about their OS not having that limitation...
...oops... no, wait... I'll be retired by then.
July 13, 2009 at 10:43 am
Retired!?
You lucky bastard...
N 56°04'39.16"
E 12°55'05.25"
July 13, 2009 at 10:43 am
mstjean (7/13/2009)
>someone decided that 12/31/9999 was going to be the end dateOh, just GREAT.
That means when Y10K rolls around, we're gonna hafta listen to the Mac camp brag about their OS not having that limitation...
...oops... no, wait... I'll be retired by then.
Oh don't worry - they will wake us all from cryostasis to handle the problem a whole 6 months before that happens.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply