August 1, 2016 at 10:43 pm
John Mitchell-245523 (7/21/2016)
Yes! I like that. I think I prefer going with explicit dates rather than integers - it makes it easier to see at a glance what it's doing. But like I said, that's just a personal preference.
SELECT DATEADD(MM, DATEDIFF(MM, '19020401', GETDATE()), '19000101')John
It might be better for your eyes, but a conversion of '19000101' to zero-date takes about 20 times longer than a conversion of integer zero to the same zero-date.
It might be due to the fact that integer zero is stored in the database as exactly the same 4 bytes binary string as date part of zero date.
And also probably due to quite expensive (in terms of CPU cycles) collation rules which get applied when any character value get processed.
Therefore
DATEDIFF(MM, 0, GETDATE())-27
will be executed much faster than
DATEDIFF(MM, '19020401', GETDATE())
_____________
Code for TallyGenerator
August 1, 2016 at 10:47 pm
ben.brugman (8/1/2016)
From this I understood that on the first off April the data should change.So '20160331' would become 2013-04-01 (Two years and something, but less than 3 years)
And '20160401' would become 2014-04-01 (Two years exactly)
This is what I understood from the TP. But I might be wrong, the question was and is not totally clear.
Ben
If your understanding is correct then this should work for you:
SELECT DATEADD(month,(DATEDIFF(MONTH, 0, Today)-27)/12*12+3,0)
FROM (
SELECT CONVERT(DATETIME, '20160401')
UNION
SELECT CONVERT(DATETIME, '20160331')
) DT (Today)
_____________
Code for TallyGenerator
August 2, 2016 at 7:51 am
Sergiy (8/1/2016)
ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the databaseDates definitively have a format when they are stored in the database.
Dates do not have format stored in the database.
Full stop.
End of story.
All date-related data types store the dates as binary strings, no place is reserved for formatting.
Yes date-related data types are stored in a binary format.
A binary format is a format as wel.
And as said it is important to know the format and the conventions, because they differ between Oracle/SQL-server/Excell and a number of other 'storage' formats.
Why should we know the 'formats',because there a differences for example in SQL-server the date (datetime2) 1500-02-29 does not exist, while at the time the date did exist.
In Excel the date 1900-02-29 does exist, while at the time it did not exist in most western countries.
And for me and most others a binary format is still a format.
For the solution with the date's changing on the first of april, I am not the topic starter, I only remarked that the given solution was not wat the topic starter requested, I think (but am not sure).
Ben
Most implementations of a date / time storage comes with it's problems.
Often this has to do with leap years and leap days.
Almost none include the leap seconds. (up to now there are 27 leap seconds and one will be added at the end of this year).
Some have to do with the granulaty of the storage format.
For SQL-server the datetime storage format is the number of microseconds since 1900-01-01 divided by three and rounded to the nearest microsecond.
I do not know what the storage format is for datetime2.
August 2, 2016 at 8:07 am
Perhaps we can all agree on the following?
* There are storage formats and presentation formats
* The user/programmer cannot change the storage format, but he can change the presentation format
* The original poster was asking about the presentation format
* This is of far more interest to us than it is to the original poster, who hasn't been seen here for 11 days!
John
August 2, 2016 at 8:37 am
John Mitchell-245523 (8/2/2016)
Perhaps we can all agree on the following?* This is of far more interest to us than it is to the original poster, who hasn't been seen here for 11 days!
There is a possibility that the topic starter did not have an oppertunity to react. But it would have been polite of him/her to react to the postings. Doesn't matter if he/she allready has a solution or not, a reaction would have been polite.
Although the 'first' party in a question is the topic starter, very often a lot of topics become of interrest to others as wel. This might be now or in the future.
* There are storage formats and presentation formats
Agreed.
* The user/programmer cannot change the storage format, but he can change the presentation format
Agreed. *)
* The original poster was asking about the presentation format
Agreed; and for the calculation of the 'date', the question was not totaly clear.
Ben
*)
For datetime2 I think the user/programmer has some influence on the storage format with the optional user-specified precision.
August 2, 2016 at 8:54 am
Sergiy (8/1/2016)
ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the databaseDates definitively have a format when they are stored in the database.
Dates do not have format stored in the database.
Full stop.
End of story.
All date-related data types store the dates as binary strings, no place is reserved for formatting.
Dates are stored as a datatype like everything else. Datatypes and formats are two different things.
Sergiy, this chunk of code may well be simplistic but I like it because it's close enough that it closes gaps, and it's open to manipulation:
-- Datetime Components
-- 24*60*60*1000 = milliseconds in a day
SELECT
d.DateTimeIn,
x.DateTimeAsD1810, y.IntegerPart, y.FractionalPart,
DateTimeOut = DATEADD(MILLISECOND,FractionalPart*(24*60*60*1000),IntegerPart)
FROM (VALUES
(GETDATE()),
('2016-02-09 23:17:57.553'),
('1899-12-31 23:59:59.997'),
('1899-12-31 00:00:00.003')
) d (DateTimeIn)
CROSS APPLY (
SELECT DateTimeAsD1810 = CAST(DateTimeIn AS decimal(18,10))
) x
CROSS APPLY (
SELECT
IntegerPart = CAST(DateTimeAsD1810 AS INT),
FractionalPart = DateTimeAsD1810 - CAST(DateTimeAsD1810 AS INT)
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 2, 2016 at 9:56 am
ben.brugman (8/2/2016)
In Excel the date 1900-02-29 does exist, while at the time it did not exist in most western countries.
That's to maintain compatibility with Lotus 1-2-3. There's a wikipedia article about it (I know, shouldn't use Wikipedia as a primary source); there are also various Microsoft KB articles:
kb 214326 - Excel incorrectly assumes that the year 1900 is a leap year
kb 214058 - Days of the week before March 1, 1900 are incorrect in Excel
Thomas Rushton
blog: https://thelonedba.wordpress.com
August 2, 2016 at 10:13 am
ThomasRushton (8/2/2016)
That's to maintain compatibility with Lotus 1-2-3.
As often within software errors, once introduced are very difficult to eliminate. We rather define the reality as wrong and keep software 'bug' compatible. (One thing for Lotus 1-2-3 is that there are countries which actually had a 29th of februar in 1900, but then again it was on a different day).
Ben
Why shouldn't you use Wikipedia. It's very often a very trustworthy source of information. And not worse than a lot of other sources.
August 2, 2016 at 4:34 pm
ChrisM@Work (8/2/2016)
Sergiy (8/1/2016)
ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the databaseDates definitively have a format when they are stored in the database.
Dates do not have format stored in the database.
Full stop.
End of story.
All date-related data types store the dates as binary strings, no place is reserved for formatting.
Dates are stored as a datatype like everything else. Datatypes and formats are two different things.
Sergiy, this chunk of code may well be simplistic but I like it because it's close enough that it closes gaps, and it's open to manipulation:
-- Datetime Components-- 24*60*60*1000 = milliseconds in a day
SELECT
d.DateTimeIn,
x.DateTimeAsD1810, y.IntegerPart, y.FractionalPart,
DateTimeOut = DATEADD(MILLISECOND,FractionalPart*(24*60*60*1000),IntegerPart)
FROM (VALUES
(GETDATE()),
('2016-02-09 23:17:57.553'),
('1899-12-31 23:59:59.997'),
('1899-12-31 00:00:00.003')
) d (DateTimeIn)
CROSS APPLY (
SELECT DateTimeAsD1810 = CAST(DateTimeIn AS decimal(18,10))
) x
CROSS APPLY (
SELECT
IntegerPart = CAST(DateTimeAsD1810 AS INT),
FractionalPart = DateTimeAsD1810 - CAST(DateTimeAsD1810 AS INT)
) y
I like simplistic scripts.
They bring to the point, without unnecessary background noice.
But I'd like to add a bit of complexity to this one. 🙂
Well, not quite complexity, but more to the presentation:
SELECT
...{as in original script}
x.DateTimeAsBin ,
CONVERT(INT, SUBSTRING(x.DateTimeAsBin, 1,4)) Bin_IntPart,
CONVERT(INT, SUBSTRING(x.DateTimeAsBin, 5,4))/24/60/60 Bin_FractPart,
.....
CROSS APPLY (
SELECT DateTimeAsD1810 = CAST(DateTimeIn AS decimal(18,10)),
DateTimeAsBin = CAST(DateTimeIn AS VARBINARY(10))
) x
It helps to understand that actual integer and fractional parts for negative numbers are not what they look like after conversion to INT.
_____________
Code for TallyGenerator
August 3, 2016 at 2:17 am
Sergiy (8/2/2016)
ChrisM@Work (8/2/2016)
Sergiy (8/1/2016)
ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the databaseDates definitively have a format when they are stored in the database.
Dates do not have format stored in the database.
Full stop.
End of story.
All date-related data types store the dates as binary strings, no place is reserved for formatting.
Dates are stored as a datatype like everything else. Datatypes and formats are two different things.
Sergiy, this chunk of code may well be simplistic but I like it because it's close enough that it closes gaps, and it's open to manipulation:
-- Datetime Components-- 24*60*60*1000 = milliseconds in a day
SELECT
d.DateTimeIn,
x.DateTimeAsD1810, y.IntegerPart, y.FractionalPart,
DateTimeOut = DATEADD(MILLISECOND,FractionalPart*(24*60*60*1000),IntegerPart)
FROM (VALUES
(GETDATE()),
('2016-02-09 23:17:57.553'),
('1899-12-31 23:59:59.997'),
('1899-12-31 00:00:00.003')
) d (DateTimeIn)
CROSS APPLY (
SELECT DateTimeAsD1810 = CAST(DateTimeIn AS decimal(18,10))
) x
CROSS APPLY (
SELECT
IntegerPart = CAST(DateTimeAsD1810 AS INT),
FractionalPart = DateTimeAsD1810 - CAST(DateTimeAsD1810 AS INT)
) y
I like simplistic scripts.
They bring to the point, without unnecessary background noice.
But I'd like to add a bit of complexity to this one. 🙂
Well, not quite complexity, but more to the presentation:
SELECT...{as in original script}
x.DateTimeAsBin ,
CONVERT(INT, SUBSTRING(x.DateTimeAsBin, 1,4)) Bin_IntPart,
CONVERT(INT, SUBSTRING(x.DateTimeAsBin, 5,4))/24/60/60 Bin_FractPart,
.....
CROSS APPLY (
SELECT DateTimeAsD1810 = CAST(DateTimeIn AS decimal(18,10)),
DateTimeAsBin = CAST(DateTimeIn AS VARBINARY(10))
) x
It helps to understand that actual integer and fractional parts for negative numbers are not what they look like after conversion to INT.
A bit Schubert's 8th for my liking. So...
SELECT
d.DateTimeIn,
x.DateTimeAsD1810, y.IntegerPart, y.FractionalPart,
DateFromD1810 = DATEADD(MILLISECOND,FractionalPart*(24*60*60*1000),IntegerPart),
'#' '#',
x.DateTimeAsBin, y.Bin_IntPart, y.Bin_FractPart,
DateFromBin = DATEADD(MILLISECOND,Bin_FractPart/0.3,Bin_IntPart)
FROM (VALUES
(GETDATE()),
('2016-02-09 23:17:57.553'),
('1900-01-01 23:59:59.997'),
('1899-12-31 23:59:59.997'),
('1899-12-31 00:00:00.003')
) d (DateTimeIn)
CROSS APPLY (
SELECT
DateTimeAsD1810 = CAST(DateTimeIn AS decimal(18,10)),
DateTimeAsBin = CAST(DateTimeIn AS VARBINARY(8))
) x
CROSS APPLY (
SELECT
IntegerPart = CAST(DateTimeAsD1810 AS INT),
FractionalPart = DateTimeAsD1810 - CAST(DateTimeAsD1810 AS INT),
Bin_IntPart = CAST(SUBSTRING(x.DateTimeAsBin, 1,4) AS INT),
Bin_FractPart = CAST(SUBSTRING(x.DateTimeAsBin, 5,4) AS INT)
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply