Formatting a date affecting MIN

Question

Formatting a date affecting MIN

faulknerwilliam2

SSCrazy

Points: 2299
More actions
September 2, 2015 at 5:38 am

#302704

Hello
In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'
This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000
So I amended the SELECT query to:
MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'
This brings the date through as 11/06/2015, which is preferable.
But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.
I appreciate this type of question is hard to answer without your being able to run the query yourelf, but obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stevenb 14609 SSCommitted Points: 1832 More actions · Answer 1

You have converted the date to a VARCHAR, so now it's sorting alphabetically instead of by date. In other words, alphabetically would be 1, 11, 12, 2, 3 ...

Easy solution is to convert your varchar field back to a date, so it would be something like

CAST(CONVERT(VARCHAR,MyDateField,103) AS DATE)

or

Just do a straight conversion to DATE, since the DATE datatype doesn't have the time portion of the DATETIME datatype.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, or just do the conversion after calculating the minimum:

CONVERT(varchar(17),MIN(a.orderdue),103)

This has the added advantage that it only has to do the conversion once (on the minimum date) instead of on every row.

This is an example of why it's usually better to have the database engine return the raw data and let your front end application make it look pretty. I know that's not always an option, but if it is, I would recommend doing it like that.

John

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

John Mitchell-245523 (9/2/2015)
Yes, or just do the conversion after calculating the minimum:
CONVERT(varchar(17),MIN(a.orderdue),103)
This has the added advantage that it only has to do the conversion once (on the minimum date) instead of on every row.
This is an example of why it's usually better to have the database engine return the raw data and let your front end application make it look pretty. I know that's not always an option, but if it is, I would recommend doing it like that.
John

Spot on all the way around, John. +1000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 4

John Mitchell-245523 (9/2/2015)
Yes, or just do the conversion after calculating the minimum:
CONVERT(varchar(17),MIN(a.orderdue),103)
This has the added advantage that it only has to do the conversion once (on the minimum date) instead of on every row.
This is an example of why it's usually better to have the database engine return the raw data and let your front end application make it look pretty. I know that's not always an option, but if it is, I would recommend doing it like that.
John

Thanks for taking the trouble to reply.

Yes, normally I'd tidy it up in SSRS, but in this instance we were using something else and doing via SSRS wasn't possible.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 5

stevenb 14609 (9/2/2015)
You have converted the date to a VARCHAR, so now it's sorting alphabetically instead of by date. In other words, alphabetically would be 1, 11, 12, 2, 3 ...
Easy solution is to convert your varchar field back to a date, so it would be something like
CAST(CONVERT(VARCHAR,MyDateField,103) AS DATE)
or
Just do a straight conversion to DATE, since the DATE datatype doesn't have the time portion of the DATETIME datatype.

Thanks for getting back - educational re VARCHAR, I won't make that error again.