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:31 pm

#280710

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 13 posts - 1 through 12 (of 12 total)

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

I guess that would depend on exactly what date you think 20114 represents.

SELECT '20114', CAST('20114'+0 AS DATETIME)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

in what manner you want to display this '20114'

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

savycara (7/23/2013)
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?

This value can be interpreted as multiple date values (see possible dates below). What date is defined by the value "20114"?

2020-01-14 (200114 / 20200114)

2020-11-04 (201104 / 20201104)

2002-01-14 (020114 YMD / 20020114)

2014-01-02 (020114 DMY / 02-01-2014)

2014-02-01 (020114 MDY / 01-02-2014)

Because additional digits are required to get a valid date notation, the conversion fails. You need to add additional prefixing to get a valid numeric value of at least 6 but preferably 8 digits.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

HanShi (7/24/2013)
Because additional digits are required to get a valid date notation, the conversion fails. You need to add additional prefixing to get a valid numeric value of at least 6 but preferably 8 digits.

Nonsense. Here's 2 cases where no additional digits are required.

SELECT '20114', CAST('20114'+0 AS DATETIME)

,DATEADD(day, 0+RIGHT('20114', 3), DATEADD(year, 0+LEFT('20114', 2), 0))

I do agree though that clarification of the requirement is needed.

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 5

dwain.c (7/24/2013)
Nonsense. Here's 2 cases where no additional digits are required.

Ok, I shouldn't use the phrase "valid date notation" but rather something like "a more deterministic or clear date notation". Blame it on my lack of the English language (non-native) :hehe:

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

I noticed this is a double post

See http://www.sqlservercentral.com/Forums/Topic1476809-3411-1.aspx#bm1476906

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

savycara Old Hand Points: 398 More actions · Answer 7

Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)

I need this column data to be in style 101

savycara Old Hand Points: 398 More actions · Answer 8

savycara

Old Hand

Points: 398

July 24, 2013 at 7:44 am

#1635357

I tried this but it is not working

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

savycara (7/24/2013)
Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)
I need this column data to be in style 101

Hey,

Try this....

declare @var varchar(6) = '20112'

DECLARE @var1 varchar(10)

declare @month varchar(2)

IF LEN(@var) > 5

BEGIN

SET @month = RIGHT(@var,2)

SET @var1 = (select LEFT(@var,4) + REPLACE(SUBSTRING(@month,1,2),' ','0')+'01')

select CONVERT(DATETIME,@var1,101)

END

ELSE

BEGIN

SET @month = RIGHT(@var,1)

SET @var1 = (select LEFT(@var,4) + REPLACE(str(@month,2),' ','0')+'01')

select CONVERT(DATETIME,@var1,101)

END

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

savycara (7/24/2013)
Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)
I need this column data to be in style 101

Your first post states that you want output as datetime. If you want your output as style 101, it would have to be varchar or char. Which do you want? Here's datetime:

SELECT *

FROM (SELECT strDate = '20114' UNION ALL SELECT '201112') d

CROSS APPLY (SELECT strDate2 = STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01') x

CROSS APPLY (SELECT dtDate = CONVERT(DATETIME,x.strDate2,112)) y

CROSS APPLY (SELECT Result = CONVERT(DATETIME,STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01',112)) z

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

savycara Old Hand Points: 398 More actions · Answer 11

savycara

Old Hand

Points: 398

July 26, 2013 at 10:14 am

#1636230

@kapil

Thank you

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

This is a duplicate thread. Let's shift over to the other thread where there are more posts. No more posts on this thread, please. It's just dividing up the answers and making things harder to follow. Thanks folks.

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