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 RileyFounder/CTOAsked:
Who is Participating?
 
SharathConnect With a Mentor Data 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 RileyFounder/CTOAuthor Commented:
How?  I'm trying the below right now and failing
cast(datecolumn as datetime)

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 RileyFounder/CTOAuthor 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 RileyFounder/CTOAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.