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 Tuesday, July 23, 2013 3:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
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?
Post #1476810
Posted Tuesday, July 23, 2013 7:13 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: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
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!

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 #1476850
Posted Wednesday, July 24, 2013 12:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
in what manner you want to display this '20114'


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1476884
Posted Wednesday, July 24, 2013 1:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 2,255, Visits: 2,718
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’! **
Post #1476898
Posted Wednesday, July 24, 2013 1:32 AM


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: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
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!

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 #1476901
Posted Wednesday, July 24, 2013 1:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 2,255, Visits: 2,718
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)


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1476904
Posted Wednesday, July 24, 2013 2:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 2,255, Visits: 2,718
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’! **
Post #1476927
Posted Wednesday, July 24, 2013 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
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
Post #1477062
Posted Wednesday, July 24, 2013 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
I tried this but it is not working
Post #1477065
Posted Wednesday, July 24, 2013 11:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1477342
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse