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

how to convert date in varchar format into datetime Expand / Collapse
Author
Message
Posted Saturday, September 28, 2013 4:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:55 PM
Points: 64, Visits: 205
Hello Everyone,

I have table in which date is in varchar format i.e. '29/09/2013'

now I want to insert the records in table1 into table2 where datatype of datecolumn is datetime so I need to convert the date in Datetime format for that I am writing the following query:

Select convert(datetime,ltrim(rtrim(replace('28/09/2013','','')))+' 00:00:00.000') from TMP_MAPPING

but I am getting error massege:
Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

can any on help me how can I import the data into my main table.
Post #1499671
Posted Saturday, September 28, 2013 5:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
Look in books online for the format codes for CONVERT and use the one that matches the data that you have.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1499676
Posted Monday, September 30, 2013 12:30 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
Hi,
Please visit this page to know the options available in Convert function
http://technet.microsoft.com/en-us/library/ms187928.aspx
Post #1499849
Posted Monday, September 30, 2013 12:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 24, 2014 11:15 PM
Points: 463, Visits: 604
CONVERT function will help u in this.(different styling).
Post #1499858
Posted Monday, September 30, 2013 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 7:14 AM
Points: 12, Visits: 13
SELECT CONVERT(DATETIME,'29/09/2013',103)

Use the convert function and the date style that suits best for your operations.
Post #1499873
Posted Wednesday, October 2, 2013 10:29 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: Today @ 6:20 AM
Points: 3,627, Visits: 5,269
If I'm reading this right, you're not trying to CONVERT a DATETIME to a dd/mm/yyyy format, rather you're trying to go the other way.

Try this:

 WITH SampleData (d) AS
(
SELECT '29/09/2013'
UNION ALL SELECT '1/09/2013'
UNION ALL SELECT '02/4/2013'
UNION ALL SELECT '3/3/2013'
)
SELECT yy, dd, mm, CAST(yy + '-' + mm + '-' + dd AS DATETIME)
FROM SampleData
CROSS APPLY
(
SELECT yy=RIGHT(d, CHARINDEX('/', REVERSE(d))-1)
,dd=LEFT(d, CHARINDEX('/', d)-1)
,mm=REPLACE(SUBSTRING(d, CHARINDEX('/', d) + 1, 2), '/', '')
) a;





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 #1500982
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse