sybase date format

Try this one out:

Replace column and urtable with column and table names respectively

SELECT cast(datepart(mm,max(column)) AS char(2)) + ':' +  cast(datepart(dd,max(column))  AS char(2))+ ':' + cast(datepart(yy,max(column)) AS char(2)) + ':' + substring (CONVERT(char(23), max(column), 116), 11, 8)
FROM urtable 

Select allOpen in new window

Thanks Much,  This is what is getting returned...

1> SELECT cast(datepart(mm,max(executiondatetime)) AS char(2))+ ':' +  
2>        cast(datepart(dd,max(executiondatetime)) AS char(2))+ ':' +
3>        cast(datepart(yy,max(executiondatetime)) AS char(4))+ ':' +
4>        substring (CONVERT(char(23), max(executiondatetime), 116), 11, 8)
5>
6> FROM jobhistory
7> go
                                   
 ----------------------------------
 4 :22:2009:9 13:55:                

The output from the original query says... how did you get the minutes and seconds?

1> Select max(executiondatetime) from jobhistory where jobid = 1500
2> go
                           
 --------------------------
        Apr 22 2009  3:00AM

its that extra nine, trying to figure out to remove.

got the 9 figured out... trying to get the 4 to read 04...

What Sybase product and exact version are you using?

Adaptive Server Enterprise/12.5.3/EBF 12332 ESD#1/P/RS6000/AIX 4.3.3/ase1253/1900/32-bit/FBO/Tue Jan 25 02:28:04 2005

thanks!

One minor syntax error, Try this one out:

SELECT cast(datepart(mm,max(column)) AS char(2)) + ':' +  
       cast(datepart(dd,max(column))  AS char(2))+ ':' + 
       cast(datepart(yy,max(column)) AS char(2)) + ':' + 
       substring (CONVERT(char(23), max(column), 116), 13, 8)
FROM urtable 

Select allOpen in new window

crap no joy, still doing the same thing...  the 4 isn't showing as 04.

1> SELECT cast(datepart(mm,max(executiondatetime))  AS char(2)) + ':' +  
2>        cast(datepart(dd,max(executiondatetime))  AS char(2)) + ':' +
3>        cast(datepart(yy,max(executiondatetime))  AS char(4)) + ':' +
4>        substring (CONVERT(char(23), max(executiondatetime), 116), 13, 8)
5>
6> FROM jobhistory
7>
8> WHERE jobid = 1500
9>
10>
11>
12> go
                                   
 ----------------------------------
 4 :23:2009:05:00:58                

This should help you out:

SELECT case when len(cast(datepart(mm,max(column)) AS varchar(2))) = 1 
then '0' + cast(datepart(mm,max(column)) AS varchar(2))
else cast(datepart(mm,max(column)) AS varchar(2)) end 
 + ':' +  
case when len(cast(datepart(dd,max(column))  AS varchar(2))) = 1 
then '0' + cast(datepart(dd,max(column))  AS varchar(2))
else cast(datepart(dd,max(column))  AS varchar(2)) end 
+ ':' + 
       cast(datepart(yy,max(column)) AS char(2)) + ':' + 
       substring (CONVERT(char(23), max(column), 116), 13, 8)
FROM urtable 

Select allOpen in new window

First, formatting should normally be done in the application, not in the database.

Second, with version 15, there are some new styles including one that would work almost unchanged.

Finally, if you pre-pend the month with a '0' and then take the right 2 characters, you will get the leading zero that you want.

Regards,
Bill

SELECT right('0'+cast(datepart(mm,max(column)),2) AS varchar(2)) + ':' +  
       cast(datepart(dd,max(column))  AS char(2))+ ':' + 
       cast(datepart(yy,max(column)) AS char(2)) + ':' + 
       substring (CONVERT(char(23), max(column), 116), 13, 8)
FROM urtable

Select allOpen in new window

Thanks, got side tracked with something else... still getting an error though... hope to get back to this later today.

1> SELECT right('0'+cast(mm,datepart(max(executiondatetime)),2) AS varchar(2)) + ':' +
2>        cast(datepart(dd,max(executiondatetime))  AS char(2))+ ':' +
3>        cast(datepart(yy,max(executiondatetime)) AS char(2)) + ':' +
4>        substring (CONVERT(char(23), max(executiondatetime), 116), 13, 8)
5>
6> FROM jogbhistory
7>
8> Where jobid = 1500
9> go
Msg 11021, Level 16, State 3:
Server 'voltsj_firstrade', Line 1:
Function DATEPART invoked with wrong number or type of argument(s).

Thanks guys for everyone's time and help on this, many apologies on getting back on this... just absolutely got buried.