Date Calculation Sorting

[gatorfe]

Hello, I have a calculation were I am extracting the month out of a date field.  When I sort this calculated field it does not sort 1,2,3,4,5,6,7,8,9,10,11,12. It does 10,11,12,1,2,3,4,5,6,7,8,9.  The calculation reads Cast(extract (month,[Invoice Date]),varchar(10)).  Is there anything I can add to calculation to make it sort as 1,2,3,4 etc order?  Thanks in advance for any suggestions.

[Lynn]

Does it have to be varchar? If you remove the cast and just extract the month it will be an integer and should sort correctly.

Otherwise you could left pad with zero so you end up with 01, 02, etc. The syntax for that could vary depending on what database functions you have available.

For me (in the DB2 torture chamber) I do this by taking the two right-most digits of a concatenation:

right ( '00' || Cast( extract ( month, [Invoice Date] ), varchar(10) ) , 2 )

[gatorfe]

Thank you Lynn!