Question Re: Why does the "DateFormat" function return incorrect results? in Technology Q&A

Why does the "DateFormat" function return incorrect results?

Former Member — Tue, 21 Aug 2012 16:10:43 GMT

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?

Re: Why does the "DateFormat" function return incorrect results?

justin_willey — Tue, 21 Aug 2012 17:30:28 GMT

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

Re: Why does the "DateFormat" function return incorrect results?

Breck_Carter — Tue, 21 Aug 2012 17:47:45 GMT

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'

Re: Why does the "DateFormat" function return incorrect results?

justin_willey — Tue, 21 Aug 2012 18:26:56 GMT

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.

Re: Why does the "DateFormat" function return incorrect results?

Former Member — Wed, 22 Aug 2012 11:49:40 GMT

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.

Re: Why does the "DateFormat" function return incorrect results?

justin_willey — Wed, 22 Aug 2012 11:59:40 GMT

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

Re: Why does the "DateFormat" function return incorrect results?

MarkCulp — Wed, 22 Aug 2012 12:03:44 GMT

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

Re: Why does the "DateFormat" function return incorrect results?

Former Member — Thu, 23 Aug 2012 15:38:23 GMT

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