October 12, 2020 at 7:20 pm
Hi,
How can I convert SCHEDSTART varchar(25) '2020-06-07-12.30.00.00000' to datetime '2020-06-07 12:30:00.000'
SELECT convert(datetime,(left([SCHEDSTART],10) +' ' + left(Right([SCHEDSTART],14), 12) ), 127) didnt work.
Any help ?
October 12, 2020 at 9:09 pm
Ugly, but this works:
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.00000';
SELECT @SchedStart
,CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':') AS DATETIME);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 12, 2020 at 9:18 pm
Declare @schedStart varchar(25) = '2020-06-07-12.30.00.00000';
Select @schedStart
, stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':')
, cast(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':') As datetime2)
, cast(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':') As datetime2(3))
, cast(left(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':'), len(@schedStart) - 2) As datetime)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2020 at 12:00 am
Just a different take on the same subject...
If the dates and times always follow the same format, which would also be required when using STUFF, then the hidden ANSI capabilities of the DATETIME datatype make all of this relatively simple.
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.0000000';
SELECT CONVERT(DATETIME,LEFT(@SchedStart,10))+REPLACE(SUBSTRING(@SchedStart,12,12),'.',':');
I suspect that Phil's method may be better performing, though I've not tested it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2020 at 12:06 am
Ugly, but this works:
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.00000';
SELECT @SchedStart
,CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':') AS DATETIME);
Dunno... that's not do ugly to me.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2020 at 3:14 am
Ah... almost forgot. One of the things that MS did very, VERY wrong is how they interpret the (secondary ANSI) format of YYYY-MM-DD when used for direct conversions from strings to the DATETIME datatype in some languages. This produces a "silent failure" in Phil's code, my code, and the 3rd method of Jeffreys's code. The silent failure is that date strings that look like they follow the ANSI format of YYYY-MM-DD are actually interpreted to be in the YYYY-DD-MM format in certain languages such as French. One of the few good things about the DATETIME2() datatype is that it appears they've fixed that issue.
Try it for yourself and see. Notice the transposition of the MM-DD part to be DD-MM as a silent failure.
SET LANGUAGE 'FRENCH';
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.0000000';
--===== Phil's method (silently fails)
SELECT Phil = CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':') AS DATETIME)
;
--===== Jeff's method (silently fails)
SELECT Jeff = CONVERT(DATETIME,LEFT(@SchedStart,10))+REPLACE(SUBSTRING(@SchedStart,12,12),'.',':')
;
--===== Jeffrey's methods (3rd method silently fails)
Select Jeffrey1 = cast(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':') As datetime2)
Select Jeffrey2 = cast(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':') As datetime2(3))
Select Jeffrey3 = cast(left(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':'), len(@schedStart) - 2) As datetime)
;
Of course, there will be a failure if a number larger than 12 is used in what will become the DD part of the format.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2020 at 3:24 am
Scott? Again???? :):):)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2020 at 3:56 pm
<headdesk> 😀 Fixed the post.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2020 at 4:22 pm
Ah... almost forgot. One of the things that MS did very, VERY wrong is how they interpret the (secondary ANSI) format of YYYY-MM-DD when used for direct conversions from strings to the DATETIME datatype in some languages. This produces a "silent failure" in Phil's code, my code, and the 3rd method of Jeffreys's code. The silent failure is that date strings that look like they follow the ANSI format of YYYY-MM-DD are actually interpreted to be in the YYYY-DD-MM format in certain languages such as French. One of the few good things about the DATETIME2() datatype is that it appears they've fixed that issue.
Try it for yourself and see. Notice the transposition of the MM-DD part to be DD-MM as a silent failure.
SET LANGUAGE 'FRENCH';
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.0000000';
--===== Phil's method (silently fails)
SELECT Phil = CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':') AS DATETIME)
;
--===== Jeff's method (silently fails)
SELECT Jeff = CONVERT(DATETIME,LEFT(@SchedStart,10))+REPLACE(SUBSTRING(@SchedStart,12,12),'.',':')
;
--===== Jeffrey's methods (3rd method silently fails)
Select Jeffrey1 = cast(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':') As datetime2)
Select Jeffrey2 = cast(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':') As datetime2(3))
Select Jeffrey3 = cast(left(stuff(stuff(stuff(@schedStart, 11, 1, ' '), 14, 1, ':'), 17, 1, ':'), len(@schedStart) - 2) As datetime)
;Of course, there will be a failure if a number larger than 12 is used in what will become the DD part of the format.
All of this is true - and there is an easy fix...change the space to a 'T' and even the datetime method will be interpreted correctly (assuming the incoming data is really YYYY-MM-DD).
Set language 'French';
Declare @schedStart varchar(25) = '2020-06-07-12.30.00.00000';
Select @schedStart
, stuff(stuff(stuff(@schedStart, 11, 1, 'T'), 14, 1, ':'), 17, 1, ':')
, cast(stuff(stuff(stuff(@schedStart, 11, 1, 'T'), 14, 1, ':'), 17, 1, ':') As datetime2)
, cast(stuff(stuff(stuff(@schedStart, 11, 1, 'T'), 14, 1, ':'), 17, 1, ':') As datetime2(3))
, cast(left(stuff(stuff(stuff(@schedStart, 11, 1, 'T'), 14, 1, ':'), 17, 1, ':'), len(@schedStart) - 2) As datetime)
I should also point out that my solution for datetime assumes only 4 digits for sub-seconds and Phil's does not...if the data is consistent it won't matter, but if the data can have a variable number of digits then Phil's code would be better.
Jeff's solution can also be corrected with a simple fix:
DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.0000000';
SELECT CONVERT(DATETIME,REPLACE(LEFT(@SchedStart,10),'-',''))+REPLACE(SUBSTRING(@SchedStart,12,12),'.',':');
By removing the '-' so the convert is on YYYYMMDD it will be interpreted correctly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 13, 2020 at 8:19 pm
To keep the record straight, the ANSI/ISO SQL standards allow only the ISO – 8601 "YYYY-MM-DD" format for dates. The. The reasons we pick this one were:
1) it's not ambiguous, and can be read directly by a parser. Allowing other formats means extra work and ambiguity
2) it sorts naturally as a display string
3) it is the most common ISO standard, after the metric system. Everyone can read it, while things that used a three letter month abbreviation or other language-dependent components are not. Look up the names of the months in Czech and in Slovak. They are radically different and yet those languages used to be part of the same country, not all that long ago.
4) display formatting is supposed to be done in the presentation layer, and it never in the database.
Personally, I like the use of "T" as a separator between the date and time components. Otherwise, we use a space and I'm not quite sure what a "space" means (blank, tab,
new line, carriage return, or some other whitespace I've forgotten about)
Please post DDL and follow ANSI/ISO standards when asking for help.
October 13, 2020 at 11:28 pm
To keep the record straight, the ANSI/ISO SQL standards allow only the ISO – 8601 "YYYY-MM-DD" format for dates. The. The reasons we pick this one were:
1) it's not ambiguous, and can be read directly by a parser. Allowing other formats means extra work and ambiguity
As you can see in the previous post, MS didn't make it unambiguous. Despite what the ANSI/ISO SQL standard may state, MS made it "language dependent" (for the DATETIME datatype, anyway. Heh... you've made several claims that you "were there" for all of this... why on earth didn't you stop them from making this horrible mistake???). So far as I'm concerned, the format of "YYYY-MM-DD" should never be used for data transmission because, in many of the languages available in SQL server, it is automatically assumed to be "YYYY-DD-MM". That makes it a "display format", which should never be used in a database, right? 😉 It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300 baud caused stupidity like 2 digit years to save space and transmission time.
2) it sorts naturally as a display string
So does the primary ISO 8601 standard of YYYYMMDD. 😉 Of course and according to you, you're not supposed to use formatting in SQL Server so that should be a moot point to begin with.
Ironically, the ISO 8601 standard that the ANSI/ISO SQL standard was made from states that
3) it is the most common ISO standard, after the metric system. Everyone can read it, while things that used a three letter month abbreviation or other language-dependent components are not. Look up the names of the months in Czech and in Slovak. They are radically different and yet those languages used to be part of the same country, not all that long ago.
I totally agree there but MS didn't enforce the format of "YYYY-MM-DD". Like I said, they instead made it language dependent comes to the DATETIME datatype. They fixed that problem with the introduction of the DATETIME2() datatype but they broke so many other ISO rules with that datatype that it makes it totally not worth using just to gain that small advantage for anything other than "display" purposes. 😀
4) display formatting is supposed to be done in the presentation layer, and it never in the database.
I agree that, other than in staging tables for file I/O, it should never be stored as a character value. However and like I just suggested about staging tables, there are times when there is no "presentation layer" other than T-SQL itself. That's why things like CONVERT (T-SQL) and TO_CHAR (other RDBMS flavors) exist... to handle that unavoidable eventuality, which isn't always relegated to only file I/O staging tables. In fact, I have a whole bunch of "DBA" reports were I actually use the XML functionality available to create HTML reports that are created and inserted in emails... all using just T-SQL. It also helps me a lot because I don't have to bother with nor fire up that god-awful SSRS application or any other application that may or may not be available at any given installation. T-SQL will always be there for SQL Server Installations.
Personally, I like the use of "T" as a separator between the date and time components. Otherwise, we use a space and I'm not quite sure what a "space" means (blank, tab, new line, carriage return, or some other whitespace I've forgotten about)
[/quote]
BWAAAA-HAAAA-HAAAAA! Ok, then... Since we're rendering opinions, I hate the "T" separator. I'm not suggesting for even a minute that it's wrong to use... I just hate it. It's a form of unnecessary formatting and the default of having a space makes it easier to read without violating any preachings (purist or otherwise) about not ever using T-SQL to format data. I DO totally agree that you shouldn't format dates when you're returning data to a GUI or other front-end. The front-end should do the formatting to satisfy regional display expectations.
Shifting gears a bit, I wish that they had actually standardized the underlying stored date and time as decimal values where the integer portion is the number of days since the 19000101 (with that day having a value of "0", which many types of software including some RDBMSs have adopted) and used the decimal places to represent the time of day instead of crazy things like the 1/300th of a second of the DATETIME datatype or the 100ns of the TIME and DATETIME2() datatypes. That would greatly simplify data transmission because then you'd truly not need to format characters to represent the date and time.
I'm also happy that they did give us the ability to rather easily format temporal data along with so many available formats so we don't need to go running to some other application every time we need to see temporal data in a certain format.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2020 at 11:56 pm
Shifting gears a bit, I wish that they had actually standardized the underlying stored date and time as decimal values where the integer portion is the number of days since the 19000101 (with that day having a value of "0", which many types of software including some RDBMSs have adopted) and used the decimal places to represent the time of day instead of crazy things like the 1/300th of a second of the DATETIME datatype or the 100ns of the TIME and DATETIME2() datatypes. That would greatly simplify data transmission because then you'd truly not need to format characters to represent the date and time.
I'm also happy that they did give us the ability to rather easily format temporal data along with so many available formats so we don't need to go running to some other application every time we need to see temporal data in a certain format.
This is something I definitely think was missed...a standardized numeric date. However - using 1900-01-01 as the zero date would not be my preference. I would prefer the Julian Day Number (https://en.wikipedia.org/wiki/Julian_day), which has a starting date of 4713-01-01 BC (-4712-01-01) on the Julian calendar (-4713-11-12 on the Gregorian calendar).
Using a 3-byte unsigned integer allows for years well into year 47,000+ which is more than plenty, but there are also modified/adjusted dates that are in use (e.g. rata die, modified jdn).
And - you can easily convert from the JDN to almost any calendar date (Julian, Gregorian, Islamic, Jewish, Chinese - and more).
But instead - with have a display format of YYYY-MM-DD or YYYYMMDD as the defined standard.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 14, 2020 at 8:46 am
It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300 baud caused stupidity like 2 digit years to save space and transmission time.
It wasn't "stupidity". That's rather a rude and arrogant characterization (reminds me of someone else, just can't put my finger on who :-)). One more byte per date (in packed decimal format (typical at the time), yyyy took one more byte than yy) was significant at the time, particularly in RAM, but also for disk. RAM for even large systems then was measured in MB. Both RAM and disk were extraordinarily more expensive than today. (I'll spare all of us any lecture about punched cards, since that's irrelevant.)
Also, note that smalldatetime survived into the 2000s, again because it saved bytes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2020 at 9:59 am
This was removed by the editor as SPAM
October 14, 2020 at 4:04 pm
Jeff Moden wrote:It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300 baud caused stupidity like 2 digit years to save space and transmission time.
It wasn't "stupidity". That's rather a rude and arrogant characterization (reminds me of someone else, just can't put my finger on who :-)). One more byte per date (in packed decimal format (typical at the time), yyyy took one more byte than yy) was significant at the time, particularly in RAM, but also for disk. RAM for even large systems then was measured in MB. Both RAM and disk were extraordinarily more expensive than today. (I'll spare all of us any lecture about punched cards, since that's irrelevant.)
Also, note that smalldatetime survived into the 2000s, again because it saved bytes.
I don't use the term "stupid" or it's derivatives very often so, no... I do realize that sounds arrogant but, at the time it was created, it was "stupidity" because it increased the load on transmission systems by 25% for such columns of data at the same time that they were trying to cut down on byte sizes by using 2 digit years. And the 2 digit year thing keeps ringing in my ears... that was a problem with mortgage calculations 30 years before Y2K happened. Again, sounds arrogant but that was another fine bit of stupidity because most people waited until 2000 to even begin trying to fix things.
Compared to what people do with XML and JSON, it's no longer "stupid" because of the larger mistake of those other datatypes. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply