Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

Datetime

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

013111
033111
022811
0
Nathan Riley
Asked:
Nathan Riley
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now