-2

I have varchar date in this format:

03/13/2015 : 2130

and i would like to convert it into datetime something like this:

2015-03-13 21:30:00.000

i have seen example like this but did not work for what i am looking for

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))
Ren P
  • 929
  • 9
  • 20
moe
  • 5,149
  • 38
  • 130
  • 197

5 Answers5

2

This will work assuming all date times parts are padded with 0's consistently.

DECLARE @Input VARCHAR(50);
SET @Input = '03/13/2015 : 2130';
SET @Input = LEFT(@Input, 10) + ' ' + LEFT(RIGHT(@Input, 4), 2) + ':' + RIGHT(RIGHT(@Input, 4), 2);

PRINT @Input;
PRINT CONVERT(DATETIME, @Input);
PRINT CONVERT(VARCHAR(50), CONVERT(DATETIME, @Input), 121);

Output:

03/13/2015 21:30

Mar 13 2015 9:30PM

2015-03-13 21:30:00.000

Community
  • 1
  • 1
jtimperley
  • 2,494
  • 13
  • 11
1

OP wants mmddyy and a plain convert will not work for that:

select convert(datetime,'12312009')

Msg 242, Level 16, State 3, Line 1 
The conversion of a char data type to a datetime data type resulted in 
an out-of-range datetime value

so try this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

2009-12-31 00:00:00.000

(1 row(s) affected)

Liam Hardy
  • 246
  • 1
  • 7
1

I think this is what you're looking for:

DECLARE @Date VARCHAR(20)
SET @Date = '03/13/2015 : 2130'

-- Format the date string
SET @Date = LEFT(@Date, 10) + ' ' + SUBSTRING(@Date, 14, 2) + ':' + SUBSTRING(@Date, 16, 2)

-- convert to date
Select CONVERT(varchar, CONVERT(DATETIME, @Date), 121)

SQL Fiddle

More Info

Amin
  • 763
  • 7
  • 22
0

If convert is not working for you then you can use mid to take date, month, year etc. And then use str_to_date to construct datetime in desired format.

In oracle use to_date and this stackoverflow link for taking substring

Community
  • 1
  • 1
  • The OP is using Microsoft SQL Server, so advising to use a MySQL function won't help much... – jpw Mar 14 '15 at 15:46
  • @Aludra your answer is right of course. Apparently OP needs a little more guidance on the specific platform though. – shawnt00 Mar 14 '15 at 16:20
  • Sorry for missing that point, updated my answer to include oracle related options. –  Mar 15 '15 at 14:36
0

Sql Function:

CONVERT(data_type(length),expression,style)

you can try this:

CONVERT(datetime,@varCharDate,121)

For more see this link

BenMorel
  • 34,448
  • 50
  • 182
  • 322
omer
  • 522
  • 1
  • 8
  • 26
  • The issue is that the input varchar string isn't in a format that convert will recognize. – jpw Mar 14 '15 at 15:49