Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Trying to convert varchar to datetime Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 9:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
DECLARE @d varchar(6) = '20114'
SELECT try_convert(date, substring(@d, 1, 4) +
CASE WHEN len(@d) = 5 THEN '0' ELSE '' END +
substring(@d, 5, 2) + '01')

This worked but I am not getting in 101 style that is 04/01/2011
Post #1477133
Posted Wednesday, July 24, 2013 3:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:19 PM
Points: 825, Visits: 756
You wanted to convert to datetime, didn't you? Datetime is a binary value and includes no format. If you want a text string, just add a conversion to varchar. However, formatting of date values is best done in the client, so that the user's regional settings can be respected.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1477286
Posted Wednesday, July 24, 2013 9:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 35,841, Visits: 32,512
savycara (7/24/2013)
DECLARE @d varchar(6) = '20114'
SELECT try_convert(date, substring(@d, 1, 4) +
CASE WHEN len(@d) = 5 THEN '0' ELSE '' END +
substring(@d, 5, 2) + '01')

This worked but I am not getting in 101 style that is 04/01/2011


If you don't mind a non-Ansi solution (and I don't), this is pretty easy. That, not withstanding, it would be good to heed the warnings about formatting dates on the backend.

WITH
cteTestData(D) AS
( --=== This is test data and is not a part of the solution
SELECT '20111' UNION ALL
SELECT '20112' UNION ALL
SELECT '20113' UNION ALL
SELECT '20114' UNION ALL
SELECT '20115' UNION ALL
SELECT '20116' UNION ALL
SELECT '20117' UNION ALL
SELECT '20118' UNION ALL
SELECT '20119' UNION ALL
SELECT '201110' UNION ALL
SELECT '201111' UNION ALL
SELECT '201112' UNION ALL
SELECT '20121'
)
SELECT OriginalData = D
, Converted = CONVERT(CHAR(10),DATEADD(mm,SUBSTRING(D,5,2)-1,SUBSTRING(D,1,4)),101)
FROM cteTestData
;

Results:
OriginalData Converted
------------ ----------
20111 01/01/2011
20112 02/01/2011
20113 03/01/2011
20114 04/01/2011
20115 05/01/2011
20116 06/01/2011
20117 07/01/2011
20118 08/01/2011
20119 09/01/2011
201110 10/01/2011
201111 11/01/2011
201112 12/01/2011
20121 01/01/2012



If you remove the CONVERT, it will be an ANSI solution that returns the DATETIME datatype which is better for the front-end. I wouldn't even think of permanently storing a formatted date in anything but a staging table being made ready for output to a text file.

If it were me, I'd find the person that wrote the code that created original formatted date mess and take them to the woodshed for a bit of education.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1477332
Posted Wednesday, July 24, 2013 9:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
Jeff Moden (7/24/2013)
savycara (7/24/2013)
DECLARE @d varchar(6) = '20114'
SELECT try_convert(date, substring(@d, 1, 4) +
CASE WHEN len(@d) = 5 THEN '0' ELSE '' END +
substring(@d, 5, 2) + '01')

This worked but I am not getting in 101 style that is 04/01/2011


If you don't mind a non-Ansi solution (and I don't), this is pretty easy. That, not withstanding, it would be good to heed the warnings about formatting dates on the backend.

WITH
cteTestData(D) AS
( --=== This is test data and is not a part of the solution
SELECT '20111' UNION ALL
SELECT '20112' UNION ALL
SELECT '20113' UNION ALL
SELECT '20114' UNION ALL
SELECT '20115' UNION ALL
SELECT '20116' UNION ALL
SELECT '20117' UNION ALL
SELECT '20118' UNION ALL
SELECT '20119' UNION ALL
SELECT '201110' UNION ALL
SELECT '201111' UNION ALL
SELECT '201112' UNION ALL
SELECT '20121'
)
SELECT OriginalData = D
, Converted = CONVERT(CHAR(10),DATEADD(mm,SUBSTRING(D,5,2)-1,SUBSTRING(D,1,4)),101)
FROM cteTestData
;

Results:
OriginalData Converted
------------ ----------
20111 01/01/2011
20112 02/01/2011
20113 03/01/2011
20114 04/01/2011
20115 05/01/2011
20116 06/01/2011
20117 07/01/2011
20118 08/01/2011
20119 09/01/2011
201110 10/01/2011
201111 11/01/2011
201112 12/01/2011
20121 01/01/2012



If you remove the CONVERT, it will be an ANSI solution that returns the DATETIME datatype which is better for the front-end. I wouldn't even think of permanently storing a formatted date in anything but a staging table being made ready for output to a text file.

If it were me, I'd find the person that wrote the code that created original formatted date mess and take them to the woodshed for a bit of education.


Gee whiz! And here I was thinking my STUFF solution was pretty clever.

Nice one Jeff!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1477334
Posted Friday, July 26, 2013 10:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
@Jeff Moden

Thank you..
Post #1478089
Posted Friday, July 26, 2013 8:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 35,841, Visits: 32,512
dwain.c (7/24/2013)[hrGee whiz! And here I was thinking my STUFF solution was pretty clever.

Nice one Jeff!


Thanks, Dwain. Indeed the STUFF solution is clever. I went for the super simple based on a little arcane knowledge that you don't have to select the perfect length string if you want to read to the end of the string. Instead of 5,2 for the substring start and length, I could have just as easily used 5,8000 and it would have produced the same results.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478261
Posted Monday, July 29, 2013 7:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
If your output requirements are driven by business intelligence or analytic needs, you can always use a lookup table (date dimension) as well. If you are interested, let me know, but I think these guys have the issue covered pretty well
Post #1478603
Posted Friday, August 2, 2013 1:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 11:50 PM
Points: 1, Visits: 3
Your LOGO is Fantastic how did u do || get that
please
thanks.
Post #1480285
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse