|
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? |
|
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' 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.
(21 Aug '12, 18:26)
Justin Willey
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.
(22 Aug '12, 11:49)
kwood65
Replies hidden
For numerical days of the month with no leading zeros you want 'Dd' rather than 'dd' - see the example in my answer.
(22 Aug '12, 11:59)
Justin Willey
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
(22 Aug '12, 12:03)
Mark Culp
That worked great! Sorry I missed the Dd format in the earlier example. Much appreciated!
(23 Aug '12, 15:38)
kwood65
|
|
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. 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 |