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)
Last Comment
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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-11
Happy 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.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
