Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Trying to convert varchar to datetime Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 23, 2013 3:31 PM
 Grasshopper 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 errorSyntax 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 Group: General Forum Members Last Login: Today @ 2:24 AM Points: 3,591, Visits: 5,100
 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 Group: General Forum Members Last Login: Monday, April 14, 2014 11:50 PM Points: 1,867, Visits: 2,275
Post #1476884
 Posted Wednesday, July 24, 2013 1:12 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, April 16, 2014 7:59 AM Points: 2,078, Visits: 2,410
 savycara (7/23/2013)I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following errorSyntax 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 Group: General Forum Members Last Login: Today @ 2:24 AM Points: 3,591, Visits: 5,100
 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 Group: General Forum Members Last Login: Wednesday, April 16, 2014 7:59 AM Points: 2,078, Visits: 2,410
 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 Group: General Forum Members Last Login: Wednesday, April 16, 2014 7:59 AM Points: 2,078, Visits: 2,410
 I noticed this is a double postSee 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 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 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 Group: General Forum Members Last Login: Monday, April 14, 2014 11:50 PM Points: 1,867, Visits: 2,275
 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 101Hey,Try this....`declare @var varchar(6) = '20112'DECLARE @var1 varchar(10)declare @month varchar(2)IF LEN(@var) > 5BEGINSET @month = RIGHT(@var,2)SET @var1 = (select LEFT(@var,4) + REPLACE(SUBSTRING(@month,1,2),' ','0')+'01')select CONVERT(DATETIME,@var1,101)ENDELSEBEGINSET @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

 Permissions