Datetime

When trying to cast these as datetime the conversion fails.  How can I convert them to datetime format?

013111
033111
022811
LVL 12
Nathan RileyFounderAsked:
Who is Participating?
 
SharathData EngineerCommented:
you can try like this.
select CONVERT(datetime,SUBSTRING(str_field,5,2)+LEFT(@date,4))

Open in new window

declare @date varchar(10) 
set @date = '013111'
select CONVERT(datetime,SUBSTRING(@date,5,2)+LEFT(@date,4)) -- 2011-01-31 00:00:00.000
set @date = '033111'
select CONVERT(datetime,SUBSTRING(@date,5,2)+LEFT(@date,4)) -- 2011-03-31 00:00:00.000
set @date = '022811'
select CONVERT(datetime,SUBSTRING(@date,5,2)+LEFT(@date,4)) -- 2011-02-28 00:00:00.000

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
add slashes:


01/31/11
03/31/11
02/28/11

you can use substring to parse it out.
0
 
Nathan RileyFounderAuthor Commented:
How?  I'm trying the below right now and failing
cast(datecolumn as datetime)

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
RGBDartCommented:
First, you must convert this string to one of the supported formats.
For example to  mm-dd-yy  

select convert (datetime, substring('013111', 1,2) + '-' + substring('013111', 3,2) + '-' + substring('013111', 5,2), 10)

Open in new window


will return a valid datetime.
0
 
Nathan RileyFounderAuthor Commented:
@ RGBDart:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select  cast (substring('013111', 1,2) + '-' + substring('013111', 3,2) + '-' + substring('013111', 5,2) as datetime)
0
 
RGBDartCommented:
Gallitin,

try this one

select convert (datetime, substring('013111', 1,2) + '-' + substring('013111', 3,2) + '-20' + substring('013111', 5,2), 110)

The goal is to correctly format your string.
Does this query works on your server
 
select convert (datetime, '01-31-2011', 110)

Open in new window

?
0
 
Nathan RileyFounderAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.