Need help in converting String to Date format in Sybase

Question

Hi,

I am trying to convert two types of string into date format, however not able to do either of them.

The problematic input strings with expected output are as follows:

---

Input 1: 20100614191522

Expected output 1: 6/14/2010 7:15:22 PM

---

Input 2: 2010/12

Expected output 2: 12/1/2010 12:00:00 AM

---

I tried like ,

select convert(datetime,'20100614191522',109) 

I tried with different style parameters with "convert" function . But I always get following errors. Syntax error during explicit conversion of VARCHAR value '20100614191522' to a DATETIME field. Msg: 249, Level: 16, State: 1

Can you please help me out, how to achieve the same.

Thanks in advance.

Answer 1

The style parameters that you have tried is for converting datetime data to a character type. Here you are asking for the opposite convertion.

Input 1

declare @str varchar(20)
set @str = '20100614191522'
select convert(datetime,substring(@str, 5, 2 ) + '/' + substring(@str, 7, 2 ) + '/' + substring(@str, 1, 4 )  + ' ' + substring(@str, 9, 2 )  + ':' + substring(@str, 11, 2 ) + ':' + substring(@str, 13, 2 ) )

gives

Jun 14 2010  7:15PM

Input 2

declare @str varchar(20)
set @str = '2010/12'
select convert(datetime, @str + '/01')

gives

Dec  1 2010 12:00AM