Home » Databases » Sybase » ASE » Porting MySQL’s date_format function to Sybase ASE 15.0.2

Porting MySQL’s date_format function to Sybase ASE 15.0.2

Did you ever wish Sybase ASE’s date formating capabilities weren’t so antique and unwieldy? Did you ever look at MySQL’s date_format() function and wish Sybase would do something like that? Yup, I’m still waiting for Sybase to fix this 15 year issue too. :(

In the meantime, using ASE 15.0.2’s user defined SQL function feature, I’ve been able to port the MySQL date_format() function to an user defined function:

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character

Usage:

select DATE_FORMAT(getdate(), '%T')

OUTPUT:

07:50:24
select DATE_FORMAT(getdate(), '%m/%d/%y %a %h:%i:%s %p %f')

OUTPUT:

10/10/07 Wed 08:04:24 AM 730000

Code: udf_date_format.sql

create function DATE_FORMAT (@now datetime, @format varchar(2000))
returns varchar(2000) AS
BEGIN
 select @format = str_replace(@format, '%r', '%h:%i:%s %p')
 select @format = str_replace(@format, '%T', '%H:%i:%s')
 select @format = str_replace(@format, '%b', substring(datename(mm, @now), 1, 3) )
 select @format = str_replace(@format, '%M', datename(mm, @now))
 select @format = str_replace(@format, '%a', substring(datename(dw, @now), 1, 3) )
 select @format = str_replace(@format, '%W', datename(dw, @now))
 select @format = str_replace(@format, '%w', right(replicate('0', 2) + convert(varchar(2), datepart(dw, @now) - 1), 2) )
 select @format = str_replace(@format, '%U', right(replicate('0', 2) + convert(varchar(2), datepart(wk, @now) - 1), 2) )
 select @format = str_replace(@format, '%V', right(replicate('0', 2) + convert(varchar(2), datepart(wk, @now)), 2) )

 select @format =
  case
   when datepart(dy, @now) = 1
    then str_replace(@format, '%u', right(replicate('0', 2) + convert(varchar(2), datepart(wk, @now) - 1), 2))
    else str_replace(@format, '%u', right(replicate('0', 2) + convert(varchar(2), datepart(wk, dateadd(dw, -1, @now) ) - 1 ), 2) )
  end

 select @format =
  case
   when datepart(dy, @now) = 1
    then str_replace(@format, '%v', right(replicate('0', 2) + convert(varchar(2), datepart(wk, @now)), 2))
    else str_replace(@format, '%v', right(replicate('0', 2) + convert(varchar(2), datepart(wk, dateadd(dw, -1, @now) )), 2) )
  end

 select @format = str_replace(@format, '%c', right(replicate('0', 2) + convert(varchar(2), datepart(mm, @now)), 2))
 select @format = str_replace(@format, '%m', right(replicate('0', 2) + convert(varchar(2), datepart(mm, @now)), 2))
 select @format = str_replace(@format, '%d', right(replicate('0', 2) + convert(varchar(2), datepart(dd, @now)), 2))
 select @format = str_replace(@format, '%e', convert(varchar(2), datepart(dd, @now)))
 select @format = str_replace(@format, '%y', substring( convert(varchar(4), datepart(yy, @now)), 3, 2))
 select @format = str_replace(@format, '%Y', convert(varchar(4), datepart(yy, @now)))
 select @format = str_replace(@format, '%X', convert(varchar(4), datepart(yy, @now)))
 select @format = str_replace(@format, '%x', convert(varchar(4), datepart(yy, @now)))
 select @format = str_replace(@format, '%H', right(replicate('0', 2) + convert(varchar(2), datepart(hh, @now)), 2))

 select @format =
  case
   when datepart(hh, @now) > 12
    then str_replace(@format, '%h', right(replicate('0', 2) + convert(varchar(2), datepart(hh, @now) - 12), 2))
    else str_replace(@format, '%h', right(replicate('0', 2) + convert(varchar(2), datepart(hh, @now)), 2))
  end

 select @format =
  case
   when datepart(hh, @now) > 12
    then str_replace(@format, '%I', right(replicate('0', 2) + convert(varchar(2), datepart(hh, @now) - 12), 2))
    else str_replace(@format, '%I', right(replicate('0', 2) + convert(varchar(2), datepart(hh, @now)), 2))
  end

 select @format = str_replace(@format, '%k', convert(varchar(2), datepart(hh, @now)))

 select @format =
  case
   when datepart(hh, @now) > 12
    then str_replace(@format, '%l', convert(varchar(2), datepart(hh, @now) - 12))
    else str_replace(@format, '%l', convert(varchar(2), datepart(hh, @now)))
  end

 select @format =
  case
   when datepart(hh, @now) > 12
    then str_replace(@format, '%p', 'PM')
    else str_replace(@format, '%p', 'AM')
  end

 select @format = str_replace(@format, '%i', right(replicate('0', 2) + convert(varchar(2), datepart(mi, @now)), 2))
 select @format = str_replace(@format, '%s', right(replicate('0', 2) + convert(varchar(2), datepart(ss, @now)), 2))
 select @format = str_replace(@format, '%S', right(replicate('0', 2) + convert(varchar(2), datepart(ss, @now)), 2))
 select @format = str_replace(@format, '%f', right(replicate('0', 6) + convert(varchar(6), datepart(ms, @now) * 1000), 6))
 select @format = str_replace(@format, '%j', right(replicate('0', 3) + convert(varchar(3), datepart(dy, @now)), 3))
 select @format = str_replace(@format, '%%', '%')

 RETURN @format
END
Share Button

4 thoughts on “Porting MySQL’s date_format function to Sybase ASE 15.0.2

  1. hendra says:

    I want to ask about query date for take month form date(dd/mm/yyyy). In my report only month will be view.
    Thank’s please give the answer to my email hendrajuve@gmail.com.

  2. Here is a good website for formatting dates using the MySQL date_format function

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Facebook login by WP-FB-AutoConnect