Need Help to convert DATETIME result into DATE on SQL Server
-
How can I convert the result into a DATE datatype as '0001-01-01' isn't supported with DATETIME? I've tried DATETIME2 but not working and the StartDate needs to be '0001-01-01'. Any help with this is highly appreciated.
DECLARE @CurrentDate DATE = '0001-01-01'SELECT
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),0),120) AS FirstMondayOftheMonth -
Your issue is that the Date supplied '0001-01-01' results in out of range error, as it is less than the minimum DATE value accepted.
Minimum Date should beDECLARE @CurrentDate DATE = '1753-01-01'or higher.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle - Nexana - Monday, July 9, 2018 8:37 PM
As Henrico suggests, this just isn't possible. SQL Server simply will NOT work with any date value prior to January 1st, 1753. As there's no normal practical use for such a date, most such attempts are the result of trying to come up with a clever way to manipulate dates with math or using character strings. If you can supply your "why", we can probably find an alternate solution for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, July 10, 2018 6:17 AM
Not sure what you're talking about. The DATE datatype works just fine with '0001-01-01'. In fact, it's the first boundary for DATE just like the first boundary for DATETIME is '1753-01-01'.
If you're saying that DATETIME won't work with '0001-01-01', then I totally agree but SQL Server WILL work with that date given the correct datatype.
--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) - Nexana - Monday, July 9, 2018 8:37 PM
The problem that you're having has to do with implicit casts. The DATE datatype cannot do integer math like the much smarter and more useful DATETIME datatype can and so any time you do such integer math like when using "0" instead of '1900-01-01' converted to a date, you'll get a failure. If we replace all your "0" integers and '-1" integers with the preconverted DATEs for those values, your code will work just fine.
DECLARE @CurrentDate DATE = '0001-01-01'SELECT
DATEADD(QQ, DATEDIFF(QQ, CONVERT(DATE,'1900-01-01'), @CurrentDate), CONVERT(DATE,'1900-01-01') )AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, CONVERT(DATE,'1899-12-31'), @CurrentDate), CONVERT(DATE,'1899-12-31')) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, CONVERT(DATE,'1900-01-01'), DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),CONVERT(DATE,'1900-01-01')),120) AS FirstMondayOftheMonth
Results:
You can also cheat on the notation a bit to make it simpler (and possibly a bit slower).
DECLARE @CurrentDate DATE = '0001-01-01'
,@0 DATE = '1900-01-01'
,@Minus1 DATE = '1899-12-31'
;
SELECT
DATEADD(QQ, DATEDIFF(QQ, @0, @CurrentDate), @0)AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, @Minus1, @CurrentDate), @Minus1) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, @0, DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),@0),120) AS FirstMondayOftheMonth
;--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) -
Just a quick note, DATE data type is stored as 3 byte little-endian, which has number of days after 0001-01-01. This means that i.e. 0001-02-01 = 0x1F0000 when in the right byte order is should be 0x00001F. It is therefore obvious that any direct numerical calculation or manipulation of the DATE data type is a futile exercise.
😎Here is an example of the first day of each of the first 13th moths
N DT RAW_BIN BYTE3 BYTE2 BYTE1 REV_BIN INT_VAL
0 0001-01-01 0x000000 0x00 0x00 0x00 0x000000 0
1 0001-02-01 0x1F0000 0x00 0x00 0x1F 0x00001F 31
2 0001-03-01 0x3B0000 0x00 0x00 0x3B 0x00003B 59
3 0001-04-01 0x5A0000 0x00 0x00 0x5A 0x00005A 90
4 0001-05-01 0x780000 0x00 0x00 0x78 0x000078 120
5 0001-06-01 0x970000 0x00 0x00 0x97 0x000097 151
6 0001-07-01 0xB50000 0x00 0x00 0xB5 0x0000B5 181
7 0001-08-01 0xD40000 0x00 0x00 0xD4 0x0000D4 212
8 0001-09-01 0xF30000 0x00 0x00 0xF3 0x0000F3 243
9 0001-10-01 0x110100 0x00 0x01 0x11 0x000111 273
10 0001-11-01 0x300100 0x00 0x01 0x30 0x000130 304
11 0001-12-01 0x4E0100 0x00 0x01 0x4E 0x00014E 334
12 0002-01-01 0x6D0100 0x00 0x01 0x6D 0x00016D 365The highest value accepted is 9999-12-31, numerical value of 3652058 which in binary format is 0x37B9DA.
- Eirikur Eiriksson - Saturday, July 14, 2018 1:30 AM
And yet the DATETIME is stored as the number of 1/300ths of a second and both the direct integer math and the direct temporal math still works. The problem isn't with the underlying storage method. The problem is with the stupid, non-ANSI way MS wrote the code for the functions that handle (or I should say, don't correctly handle) the DATE and other supposedly "more advanced" datatypes, which are actually a throwback to the dark ages IMHO.
Ironically, MS is aware of that fact because, in a vain attempt to fix their shortsightedness, they came out with DATEDIFF_BIG instead of fixing things to allow for the ANSI standards of being able to do things like Period = EndDateTime - StartDateTime and EndDateTime = StartDateTime + Period which, as you know, the DATETIME datatype actually does quite nicely if you know how to format the Period. The following article demonstrates such a thing using DATETIME. The same methods will not work with the "newer, improved" (whatever you want to call them... I call them "crippled") temporal datatypes.
http://www.sqlservercentral.com/articles/T-SQL/103343/Sorry to rant but considering all of the temporal information and reporting required of databases in general, the "crippled" datatypes are a horrible oversight by MS.
--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, where should I start on this? Not translating the binary values, in the right order, caveats on the ranges covered, unsigned datatype as a base?
😎Datetime is a float constructed value, makes the arthimetris straight forward, the 2008 introduced data types need some work!
Posted here few years back a test harness and the new types were around 50% slower - Jeff Moden - Friday, July 13, 2018 10:00 PMNexana - Monday, July 9, 2018 8:37 PM
The problem that you're having has to do with implicit casts. The DATE datatype cannot do integer math like the much smarter and more useful DATETIME datatype can and so any time you do such integer math like when using "0" instead of '1900-01-01' converted to a date, you'll get a failure. If we replace all your "0" integers and '-1" integers with the preconverted DATEs for those values, your code will work just fine.
DECLARE @CurrentDate DATE = '0001-01-01'SELECT
DATEADD(QQ, DATEDIFF(QQ, CONVERT(DATE,'1900-01-01'), @CurrentDate), CONVERT(DATE,'1900-01-01') )AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, CONVERT(DATE,'1899-12-31'), @CurrentDate), CONVERT(DATE,'1899-12-31')) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, CONVERT(DATE,'1900-01-01'), DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),CONVERT(DATE,'1900-01-01')),120) AS FirstMondayOftheMonth
Results:You can also cheat on the notation a bit to make it simpler (and possibly a bit slower).
DECLARE @CurrentDate DATE = '0001-01-01'
,@0 DATE = '1900-01-01'
,@Minus1 DATE = '1899-12-31'
;
SELECT
DATEADD(QQ, DATEDIFF(QQ, @0, @CurrentDate), @0)AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, @Minus1, @CurrentDate), @Minus1) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, @0, DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),@0),120) AS FirstMondayOftheMonth
;@jeff: Replacing the '0' with the pre-converted DATEs worked fine without even compromising speed. Thank you so much 😀
- Eirikur Eiriksson - Saturday, July 14, 2018 12:34 PM
Just to be sure of what you're talking about... are you talking about the datatypes for the storage of DATETIME or just the methods to do the direct temporal calculations with DATETIME? I used to think that DATETIME was a float (despite what they said in BOL because it behaved like one for accuracy) but someone proved to me that Datetime is patently NOT a float constructed value. He showed me the values stored on a page and they really were INTs and I proved their content by manually doing the date math for the stored values. It is, in fact, comprised of two integers, the first being the number of whole days since 1900-01-01 and the second being the second being the number of 300th's of a second since midnight.
As good as that is, I'm still disappointed a bit in DATETIME. It, indeed, would have been great, if DATETIME had been stored as DECIMAL days where time was the fractional part of the decimal but it was invented long ago when disk space was a premium and every byte counted. Heh... as you know, it still smokes the newer datatypes for functionality.
As for the "new" types being 50% slower, you've piqued my interest. Slower doing what? I've no real hint there because I avoid them like the plague. Do you mean any form of date/time math using the date/time functions built into SQL server or joins or comparisons or ??? If you have a link for that post from a few years back, I'd love to see it because you're one of the folks that does know how to put together some excellent performance tests. And, if that link contains your typical well thought out tests, THAT would make an excellent article for you to submit!
--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) - Nexana - Saturday, July 14, 2018 5:12 PMJeff Moden - Friday, July 13, 2018 10:00 PMNexana - Monday, July 9, 2018 8:37 PM
The problem that you're having has to do with implicit casts. The DATE datatype cannot do integer math like the much smarter and more useful DATETIME datatype can and so any time you do such integer math like when using "0" instead of '1900-01-01' converted to a date, you'll get a failure. If we replace all your "0" integers and '-1" integers with the preconverted DATEs for those values, your code will work just fine.
DECLARE @CurrentDate DATE = '0001-01-01'SELECT
DATEADD(QQ, DATEDIFF(QQ, CONVERT(DATE,'1900-01-01'), @CurrentDate), CONVERT(DATE,'1900-01-01') )AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, CONVERT(DATE,'1899-12-31'), @CurrentDate), CONVERT(DATE,'1899-12-31')) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, CONVERT(DATE,'1900-01-01'), DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),CONVERT(DATE,'1900-01-01')),120) AS FirstMondayOftheMonth
Results:You can also cheat on the notation a bit to make it simpler (and possibly a bit slower).
DECLARE @CurrentDate DATE = '0001-01-01'
,@0 DATE = '1900-01-01'
,@Minus1 DATE = '1899-12-31'
;
SELECT
DATEADD(QQ, DATEDIFF(QQ, @0, @CurrentDate), @0)AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, @Minus1, @CurrentDate), @Minus1) AS LastDayOfQuarter,
CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, @0, DATEADD(DAY, (6-DATEPART(DAY, @CurrentDate)), @CurrentDate)),@0),120) AS FirstMondayOftheMonth
;@jeff: Replacing the '0' with the pre-converted DATEs worked fine without even compromising speed. Thank you so much 😀
My pleasure. Thank you for the feedback.
--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) - Nexana - Monday, July 9, 2018 8:37 PM
That is a valid DATE data type display string. There should be no need to build it from pieces like a 1960's COBOL expression. Things like the firs and last dates in a quarter ought to be in a Calendar table.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Sunday, July 15, 2018 4:41 PM
Calendar tables are certainly a convenience but the formulas can be quicker and take no reads like a Calendar table would. Since they don't rely on a Calendar table, they also have no dependencies and can be used in any database.
Heh... Imagine the conversation...
Employee: I can't do my job! There's no Calendar table in this database!
Manager: We don't own that database so we can't add a Calendar table to it.
Employee: Ok. I'll get the data from the Calendar table in one of our databases.
Manager: Can't do that. We're not allowed to go to another database for data.
Employee: I can't do my job! There's no Calendar table in this database!Yeah... lemme help you find another job. Burger King ok with you? 😉
All hail the 1960's COBOL expressions!!! A lot of the "Black Arts" of SQL Server are actually just tried and true "Old Arts" that will work well forever. 😉
--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 - Friday, July 13, 2018 9:45 PM
As always, you educate me in new ways.... I was unaware that the DATE data type would support such, but the DATETIME does not, so I had once again made an assumption that turns out to have been unwarranted, as I had always known that 1/1/1753 was the low end on DATETIME. However, from a practical perspective, just what on earth value is there in using such values for a DATE data type? Certainly not the accurate measurement of the historical timeline... And I am an absolute stickler for using the right data type for the right reason. So maybe my saying SQL Server won't work with those "date values", I'm at least "historically accurate" ? Anyway, thanks for keeping me up to date....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, July 16, 2018 7:10 AM
Thanks for the feedback, Steve. You'll get no argument from me on the subject of accuracy when it comes to dates prior to 1753. The Gregorian calendar came to be the standard across many years with seemingly one country or area at a time slowly adopting it. There's also the subject of the inherent date errors that built up over time with the Julian and other calendars. Heh... then there's the subject of why there is no year 0000. 😉
--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)
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply