Why “3653.0 and 36524.0”?

Question

Why “3653.0 and 36524.0”?

jshahan

SSCarpal Tunnel

Points: 4622
More actions
December 8, 2014 at 10:44 am

#293152

I see this syntax in Jeff M’s work and others all over the Internet.
select CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
Not difficult to see WHAT is happening but WHY are these particular constant values (3653.0,36524.0) used? I’ve searched a great deal and found nothing whatever so I guess the reason must be obvious to most people.
Not me though. Would someone mind educating me?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

SELECT CAST(0 AS DATETIME)

Will give you 1/1/1900, the RAND(CHECKSUM(NEWID()))*3653.0 basically give you a 10 year date range from that so between1/1/1900 and 1/2/1910, then adding 36524 adds 100 years to that.

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 2

SELECT CAST(0 AS DATETIME)
Will give you 1/1/1900, the RAND(CHECKSUM(NEWID()))*3653.0 basically give you a 10 year date range from that so between1/1/1900 and 1/2/1910, then adding 36524 adds 100 years to that.

Thank you very much. That explains why those constants are popular.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Quick joke, 36524 is the number of versions Jeff needs to upgrade on his old test box 😀

😎

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

Quick joke, 36524 is the number of versions Jeff needs to upgrade on his old test box

Hopefully he'll see this

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

jshahan (12/8/2014)
Quick joke, 36524 is the number of versions Jeff needs to upgrade on his old test box
Hopefully he'll see this

36524.0 if the required value for DBCC TimeWarp paramater 79

🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

jshahan (12/8/2014)
I see this syntax in Jeff M’s work and others all over the Internet.
select CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
Not difficult to see WHAT is happening but WHY are these particular constant values (3653.0,36524.0) used? I’ve searched a great deal and found nothing whatever so I guess the reason must be obvious to most people.
Not me though. Would someone mind educating me?

That was some fairly early work on my part to try to keep things very simple and fast for building a million row test table with mostly generic examples of columns. There are, of course, better ways to write such a thing especially when it comes to the human readability part of it all. The following does the same thing as the code you posted above.

SELECT RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2010') + CAST('2000' AS DATETIME)

;

The code exploits what many consider to be a "fault" of the date/time data-types. If you present the functions with a 4 character number, T-SQL will treat it as a year and will interpret it as midnight of the first day of the year. The code also exploits another fascinating "fault" of the DATETIME data-type in that it allows the direct addition of decimal values that represent dates and time to a given DATETIME. This "fault" (which I obviously consider to be a highly useful feature and is similar to what you'd find in spreadsheets) doesn't work in the newer date/time data-types and that's why I don't use them even if I only have a date-with-no-time to work with.

For more information on how to generate constrained random dates and times, please see the following link to an article on the subject. That's followed by a link to an article on how to generate constrained random numbers. Both are great for building huge test tables in just a couple of seconds.

http://www.sqlservercentral.com/articles/Test+Data/88964/

http://www.sqlservercentral.com/articles/Data+Generation/87901/

Both are great for generating no-quite-flat random domains very quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Eirikur Eiriksson (12/8/2014)
Quick joke, 36524 is the number of versions Jeff needs to upgrade on his old test box 😀
😎

Heh... better joke... that's the number of times I've heard that joke. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 8

The code exploits what many consider to be a "fault" of the date/time data-types. If you present the functions with a 4 character number, T-SQL will treat it as a year and will interpret it as midnight of the first day of the year. The code also exploits another fascinating "fault" of the DATETIME data-type in that it allows the direct addition of decimal values that represent dates and time to a given DATETIME. This "fault" (which I obviously consider to be a highly useful feature and is similar to what you'd find in spreadsheets) doesn't work in the newer date/time data-types and that's why I don't use them even if I only have a date...

Thanks much for the additional info, Jeff. I make notes on all this stuff.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 9

Be careful poking fun at Jeff. I would not like to get hit with an RBAR! :w00t:

ATBCharles Kincaid

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Jeff doesn't hit people with RBAR, he actively campaigns against it. High-velocity pork chops, on the other hand, are fair game. 😀

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

jshahan (12/9/2014)
The code exploits what many consider to be a "fault" of the date/time data-types. If you present the functions with a 4 character number, T-SQL will treat it as a year and will interpret it as midnight of the first day of the year. The code also exploits another fascinating "fault" of the DATETIME data-type in that it allows the direct addition of decimal values that represent dates and time to a given DATETIME. This "fault" (which I obviously consider to be a highly useful feature and is similar to what you'd find in spreadsheets) doesn't work in the newer date/time data-types and that's why I don't use them even if I only have a date...
Thanks much for the additional info, Jeff. I make notes on all this stuff.

Slight omission on my part. I said "4 character number" to imply that it must be a character type and not a numeric. If you give a an actual 4 digit integer (or any other numeric data type), SQL Server interpret that as the number of days since the first of January 1900.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Ed Wagner (12/9/2014)
Jeff doesn't hit people with RBAR, he actively campaigns against it. High-velocity pork chops, on the other hand, are fair game. 😀

Hmmmm... maybe butterfly stuffing the pork chops with a 90^o chunk of rebar would add some impact to the pork chop. E=MC². If you can't make it go faster, make it heavier. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 13

Slight omission on my part. I said "4 character number" to imply that it must be a character type and not a numeric. If you give a an actual 4 digit integer (or any other numeric data type), SQL Server interpret that as the number of days since the first of January 1900.

I figured that was what you meant...

Charles Kincaid SSChampion Points: 13593 More actions · Answer 14

Jeff Moden (12/9/2014)
E=MC². If you can't make it go faster, make it heavier. 😀

LOL! When you make it go faster it is heavier. While SQL is my job QM is my hobby.

ATBCharles Kincaid