Why does the "DateFormat" function return incorrect results?

[Former Member]

I have a column (named TDate) defined as Date in a table. When I type "Select TDate, dateformat(tdate, 'm/d/yyyy') as newdate" the day portion of "newdate" is not correct (in Interactive SQL). For example, one of the rows has a date of '2012-09-15' in TDate. The Results pane shows "2012-09-15" for TDate and "9/7/2012" in the newdate column. I was hoping to see "9/15/2012" in the newdate column. Has anyone else seen this behavior?

[Breck_Carter]

d is for day number in the week (7 for Saturday), dd is for day number in the month, ddd... is for day name; see date_format.

Select '2012-09-15' as thedate, dateformat(thedate, 'm/dd/yyyy') as newdate

thedate,newdate
'2012-09-15','9/15/2012'

[justin_willey]

That makes sense! There is some confusion in the documentation - the syntax page for dateformat() points to the timestamp_format option for a list of allowable formats. The info there is a bit different from that for date_format, in particular timestamp_format doesn't mention d whereas date_format does.

I've added a comment in DocComment Exchange.

[Former Member]

Thanks for the information! After reading the documentation more closely I see where it says that for the "d" format. I'm running version 12.0.1. Is there no format to hide the leading zero on the day of the month? That's the format I'm trying to show.

[justin_willey]

For numerical days of the month with no leading zeros you want 'Dd' rather than 'dd' - see the example in my answer.

[MarkCulp]

Try using Dd for the format... as in:

select '2012-01-02' as thedate, dateformat(thedate, 'Mm/Dd/yyyy') as newdate

FWIW: This is documented on dcx - http://dcx.sybase.com/index.html#1201/en/dbadmin/timestamp-format-option.html - in the paragraph starting with "For symbols that represent numeric data...":

 Type the symbol in mixed case (such as Mm) to suppress zero padding. For example, yyyy/Mm/Dd could produce 2002/1/1

[Former Member]

That worked great! Sorry I missed the Dd format in the earlier example. Much appreciated!

[justin_willey]

You don't say which version you are using, but running:

Select '2012-09-15' as thedate, dateformat(thedate, 'm/d/yyyy') as newdate

in 10.0.1 produces:

thedate      newdate
=========    =========
2012-09-15   09/7/2012

as you say. While it doesn't explain what those results mean, the problem is that 'm/d/yyy' is not a supported date format. d means ordinal day of the week - 1= Sunday etc - see Breck's answer. To get a day and month without leading zeros you need ''Mm/Dd/yyyy' ie

Select '2012-09-15' as thedate, dateformat(thedate, 'Mm/Dd/yyyy') as newdate

which does indeed produce:

thedate      newdate
=========    =========
2012-09-15   9/15/2012

The full description of the valid formats (for v12.0.1) is here