sybase date format

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 

                                              

1:
2:
3:
4:
5:

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 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

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

                                              

1:
2:
3:
4:
5:

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