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:
OUTPUT:
07:50:24
OUTPUT:
10/10/07 Wed 08:04:24 AM 730000
Code: udf_date_format.sql
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
Pingback: Ramblings of a Geek » Blog Archive » ASE 15 stored procedure “sp_tables” doesn’t work with SQL UDFs
Pingback: unable to create a SQL UDF in Sybase ASE using a case statement | Jason L. Froebe: Ramblings of a Geek
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.
Here is a good website for formatting dates using the MySQL date_format function