Porting MySQL’s date_format function to Sybase ASE 15.0.2
ASE, Databases, How To, MySQL, Sybase Add commentsDid 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
April 11th, 2008 at 2:16 pm
[...] In the above example, the DATE_FORMAT UDF is the one at Porting MySQL’s date_format function to Sybase ASE 15.0.2 [...]
July 9th, 2008 at 1:42 pm
[...] I’ve been able to use case in SQL UDFs before - see http://froebe.net/blog/2007/10/10/porting-mysqls-date_format-function-to-sybase-ase-1502/ Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux 2.4.21-47.ELsmp [...]