Date format in Informax SQL query

[cheryl27284]

I have a field called row_date which looks like: 05/01/2005.. It's in the date format. If I use MONTH(row_date) as Mnth, it comes out as a 5. I would like to format the date as May. Does anyone know a different function I could use? Thanks!

 

[PHV]

Depending of your version, you may try this:
SELECT TO_CHAR(row_date , '%B') AS Mnth

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cheryl27284]

I'm getting a syntax error on the TO_CHAR(row_date , '%B') AS Mnt.. Is that the letter B?

 

[cheryl27284]

I finally got it to work thanks!! New issue, when I order by 1(first column) It puts the months in ABC order instead of month order. What do I do now? Thanks!

 

[olded]

Have you considered using a case statement in the select:

SELECT month(shiftdate),
       CASE
       WHEN month(shiftdate) = 1 THEN "Jan"
       WHEN month(shiftdate) = 2 THEN "Feb"
       WHEN month(shiftdate) = 3 THEN "Mar"
       .
       .
       END CASE
       FROM c_shiftacc ORDER BY 1

This adds another column to the select which might not be what you want.

 

[cheryl27284]

I have the month showing now, I just need it to sort by month and recognize that it's a date and not a text field. Right now it sorts in ABC order. Can someone help?

 

[PHV]

Simply add a MONTH(row_date) column and sort by it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886