Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1393
  • Last Modified:

sybase date format

How do I format a date value in Sybase?

Select max(executiondatetime) from jobhistory where jobid = 1500

Returns        Apr 22 2009  3:00AM.

I'd like it to return MM:DD:YYYY:HH:MI:SS
0
sanate
Asked:
sanate
  • 7
  • 4
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 

Open in new window

0
 
sanateAuthor Commented:
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

0
 
sanateAuthor Commented:
its that extra nine, trying to figure out to remove.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
sanateAuthor Commented:
got the 9 figured out... trying to get the 4 to read 04...
0
 
grant300Commented:
What Sybase product and exact version are you using?
0
 
sanateAuthor Commented:
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!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 

Open in new window

0
 
sanateAuthor Commented:
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                
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 

Open in new window

0
 
grant300Commented:
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

Open in new window

0
 
sanateAuthor Commented:

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).
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Minor syntactical error, Try this one out:
SELECT right('0'+cast(datepart(mm,max(executiondatetime)) AS varchar(2)),2) + ':' +
cast(datepart(dd,max(executiondatetime)) AS char(2))+ ':' +
cast(datepart(yy,max(executiondatetime)) AS char(2)) + ':' +
substring (CONVERT(char(23), max(executiondatetime), 116), 13, 8)
FROM jogbhistory

Open in new window

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now