convert left(datetime ,11) and order by

Question

Post reply

convert left(datetime ,11) and order by

snomadj

SSCarpal Tunnel

Points: 4491
More actions
December 3, 2009 at 4:24 am

#135623

i have a table of datetime vals. I need to get only the distinct dates (don't care about times) & order by datetime.
so i have select (left(datecolumn,11))
from tab
order by (left(datecolumn,11))
& it orders as a char (alphabetically).
apr 01 2009
dec 12 2009
feb 01 2009
etc.
how do i get it to order as datetimes?

Viewing 8 posts - 1 through 7 (of 7 total)

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

NickDBA Hall of Fame Points: 3792 More actions · Answer 1

Hi, you need to convert back to datetime in order to get the correct ordering.

Something like this..

select convert(datetime,convert(varchar(11),datecolumn),103)

order by datecolumn

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 2

Thank you!!

That's perfect, now I just need to add in a distinct & convert into the format '20 Dec 2009'.

:unsure:

I'm being a muppet with this i know.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP TABLE #Guests

CREATE TABLE #Guests(

Firstname varchar(30),

Lastname varchar(30),

Birthdate datetime

)

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Jim','Jones','1961-12-03 01:00:00.000')--Birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Joe','Bloggs','1961-12-03 02:00:00.000')--Birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Dave','Thomas','19631212')--Birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Pete','Jones','19641218')-- No birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Mike','Evans','19651225')-- No birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Sally','Evans','19660115')-- No birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Mary','Jones','19671217')--Birthday in next 2wks

INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Wendy','Smith','19680125')-- No birthday in next 2wks

SELECT MyDate

FROM (

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Birthdate)) AS MyDate

FROM #Guests

) d

GROUP BY MyDate

ORDER BY MyDate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NickDBA Hall of Fame Points: 3792 More actions · Answer 4

The problem here is that if you want SQL Server to display that date in this format DD MMM YYYY (20 Dec 2009) then you need to convert it to varchar, which will screw up the ordering, and therefore you'll need to order by something else. For example

SELECT DISTINCT

DateColumn,

CONVERT(VARCHAR(11),DateColumn,106)

FROM [some table]

ORDER BY 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

BaddaBing (12/3/2009)
The problem here is that if you want SQL Server to display that date in this format DD MMM YYYY (20 Dec 2009) then you need to convert it to varchar, which will screw up the ordering, and therefore you'll need to order by something else. For example
SELECT DISTINCT
DateColumn,
CONVERT(VARCHAR(11),DateColumn,106)
FROM [some table]
ORDER BY 1

Almost.