Convert DateTime value
I'm uploading a flat file I recieve into a temp table. A date in the file is in the format of 'dd-mm-yy' and is placed into a varchar field in the temp table. Is the only way to convert this to an actual date in SQL by using SUBSTRING like below or is there a better way? This is for SQL 2005.
CAST(SUBSTRING(F1,4,2) + '-' + SUBSTRING(F1,1,2) + '-' + SUBSTRING(F1,7,2) AS datetime)
Kevin Cross
No you can use the CONVERT() function also. You just have to provide it with the format of the string in the field.
Forgot link to reference, but here is an example also.
It looks like you need the Italian format 105.
It looks like you need the Italian format 105.
CONVERT(DATETIME, F1, 105)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may find following helpfull with Convert
http://www.w3schools.com/sql/func_convert.asp
http://www.w3schools.com/sql/func_convert.asp
ASKER
Ha, I had been trying convert with 105 for a while and kept getting the conversion failed from character string which is why I switched to substring. I had just realized I needed 5 instead of 105 when you posted mwvisa1. Thank you!!
Hi,
Here is the good DateTime conversion to any Date format...
http://blog.sqlauthority.c
after you have created the sql function specified in the above link, try the below sample sql statements for your needed format.
eg.,
Happy Programming.
Here is the good DateTime conversion to any Date format...
http://blog.sqlauthority.c
after you have created the sql function specified in the above link, try the below sample sql statements for your needed format.
eg.,
-- in your case input is dd-mm-yy (05-16-11)
SELECT [dbo].[ufsFormat] ('05-16-11', 'mm-dd-yyyy') -- Output : 05-16-2011
SELECT [dbo].[ufsFormat] ('05-16-11', 'dd-mm-yyyy') -- Output: 16-05-2011
SELECT [dbo].[ufsFormat] ('05-16-11', 'dd-mm-yy') -- Output: 16-05-11Happy Programming.
Pinal Dave writes some very great stuff, but keep in mind the need here is to go from VARCHAR to DATETIME. Pinal's UDF is going from DATETIME to a specific formatted VARCHAR.