Convert DateTime value

tim_cs
tim_cs used Ask the Experts™
on
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)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
No you can use the CONVERT() function also.  You just have to provide it with the format of the string in the field.
Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
Hi,
Please follow the link:
http://www.sql-server-helper.com/tips/date-formats.aspx
Thanks
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Forgot link to reference, but here is an example also.

It looks like you need the Italian format 105.
CONVERT(DATETIME, F1, 105)

Open in new window

http://msdn.microsoft.com/en-us/library/ms187928.aspx
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Chief Technology Officer
Most Valuable Expert 2011
Commented:
Oops.  Sorry for the extra e-mail, but when reading the reference, note the shorter number, i.e., 5 versus 105 is usually because of 2-digit year versus 4-digit one; therefore, note for dd-mm-yy:

CONVERT(DATETIME, F1, 5)

Open in new window

Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
You may find following helpfull with Convert
http://www.w3schools.com/sql/func_convert.asp

Author

Commented:
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!!
Rose BabuSenior Team Manager

Commented:
Hi,

Here is the good DateTime conversion to any Date format...

http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

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

Open in new window

Try with this. it may help you.

Happy Programming.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial