Sybase SQL: Convert String to Date and Time

Question

Sybase SQL Convert String to DateTime Help:

I have date and time stored on a table in string format ('yyyymmddhhnnss'). I need to select them to date and time format.

For example: String Value: '20141228092818' Expected to return: '28 Dec 2014 09:28:18 AM'

String Value: '20141121132810' Expected to return: '21 Nov 2014 01:28:10 PM'

Appreciate help...

possible duplicate of [How to convert a string to a date in sybase](http://stackoverflow.com/questions/56303/how-to-convert-a-string-to-a-date-in-sybase) — Mike Gardner, Feb 10 '15 at 15:42

score 3 · Accepted Answer · answered Feb 12 '15 at 09:59

The output format you have specified is not quite the same as any of the Sybase standard styles (see the Sybase documentation for the convert function).

It's quite close to the 109 style though, just the day and month reversed, and no leading zero in the time. To make this conversion, you will need to convert your string to a format that Sybase understands, and can convert to a datetime (e.g. yyyymmdd hh:mm:ss), convert it to a datetime, and then convert it back to a string using the 109 style.

Example:

create table #test (test varchar(255))

insert #test select '20141228092818'
insert #test select '20141121132810'

select
    convert(varchar(255),
        convert(datetime,
            left(test, 8) + ' ' +
            substring(test, 9, 2) + ':' +
            substring(test, 11, 2) + ':' +
            substring(test, 13, 2)
        ),
    109)
from #test

Output:

Dec 28 2014  9:28:18:000AM
Nov 21 2014  1:28:10:000PM

score 0 · Answer 2 · answered Feb 17 '15 at 16:16

Thanks Toby. It worked.

SELECT CONVERT(datetime,SUBSTRING(MyField, 5, 2 ) + '/' + SUBSTRING(MyField, 7, 2) + '/' + SUBSTRING(MyField, 1, 4 ) + ' ' + SUBSTRING(MyField, 9, 2 ) + ':' + SUBSTRING(MyField, 11, 2 ) + ':' + SUBSTRING(MyField, 13, 2 )) TransactDateTime

28/12/2014 09:28:18 AM

21/11/2014 01:28:10 PM

Sybase SQL: Convert String to Date and Time

2 Answers2