Trying to convert varchar to datetime

Question

Post reply

Trying to convert varchar to datetime

savycara

Old Hand

Points: 398
More actions
July 23, 2013 at 3:23 pm

#296673

I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following error
Syntax error during explicit conversion of VARCHAR value '20114' to a DATETIME field.
Can some one help me in solving this?

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

And what date do you expect 20114 to convert to? And by which rule?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

savycara Old Hand Points: 398 More actions · Answer 2

I need to convert it to 2011401 and need to use convert..Thank you

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

2011401 does not have a common format for a date. Can you explain which is the year, month and day?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

savycara Old Hand Points: 398 More actions · Answer 4

savycara

Old Hand

Points: 398

July 23, 2013 at 8:05 pm

#1635186

2011 is year

4 month

01 is day

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

You could try something like this

DECLARE @cDate varchar(5) = '20114'

SELECT DATEADD( MONTH, RIGHT(@cDate, 1) , CAST( LEFT(@cDate,4) + '0101' as DATE))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

savycara Old Hand Points: 398 More actions · Answer 6

Thank you..and actually it is a column that stores data as varchar and data is like this

start date

------------

20114 ----2011 is year and 4 is month

20113

20112

20105

20106

I need to represent above data as (adding date 01 to all rows)

2011401-- year month and date

2011301

2011201

2010501

2010601

that is I need to add date and convert into DATETIME and 101 style

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

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')

I assume that 201110 would be 2011-10-01

I added try_convert, since most likely you have values in that column that are illegal.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

Ignoring the try_convert (which is probably a good idea), I think this may be another way:

SELECT CONVERT(VARCHAR(10), CAST(STUFF('201311',5,0,'-0')+'-01' AS DATETIME), 101)

,CONVERT(VARCHAR(10), CAST(STUFF('20131',5,0,'-0')+'-01' AS DATETIME), 101);

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

savycara Old Hand Points: 398 More actions · Answer 9

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 Thank you

savycara Old Hand Points: 398 More actions · Answer 10

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 11

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.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

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.

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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 13

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

savycara Old Hand Points: 398 More actions · Answer 14

savycara

Old Hand

Points: 398

July 26, 2013 at 10:10 am

#1636227

@jeff Moden

Thank you..