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?

asked 21 Aug '12, 16:10

kwood65's gravatar image

kwood65
31113
accept rate: 0%

edited 21 Aug '12, 17:40

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249


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'
permanent link

answered 21 Aug '12, 17:47

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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. 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

permanent link

answered 21 Aug '12, 17:30

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

edited 22 Aug '12, 12:22

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×46
×7

question asked: 21 Aug '12, 16:10

question was seen: 3,357 times

last updated: 23 Aug '12, 15:38