Ordering dates without the minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ordering dates without the minutes

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2004-06-02 : 15:02:38

Is there an easy way to take a datetime column, and just return the date as mm/dd/yy without the minutes without converting it to a varchar which messes up how it orders?

My current statement looks like


select distinct(convert(varchar(10), PoolArrivalDate, 101)) as PoolDate
from aims.dbo.stock 
where PoolArrivalDate is not null
and PoolArrivalDate<>''
order by PoolDate desc

But of course that doesn't work properly then because the date is order as a string now.

Even if I do this instead;


distinct(convert(smalldatetime, PoolArrivalDate, 101))

the stupid thing still returns the minutes despite me explicitly stating I wanted it back in the '101' format.

Any thoughts?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-02 : 15:07:58

smalldatetime includes HH:MM no seconds.

CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime)

returns

mm/dd/yyyy 00:00:00.000

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-02 : 15:09:04

or just
CONVERT(char(8), PoolArrivalDate, 112)

yyyymmdd will be in the right order.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

label
Posting Yak Master

197 Posts

Posted - 2004-06-02 : 15:43:00

quote:
Originally posted by drymchaser

smalldatetime includes HH:MM no seconds.

CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime)

returns

mm/dd/yyyy 00:00:00.000

Ok, I tried this:


select distinct(CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime)) as PoolDate

and that does return mm/dd/yyyy 00:00:00.


2004-12-23 00:00:00.000
2004-12-22 00:00:00.000
2004-12-12 00:00:00.000
2004-06-02 00:00:00.000
2004-06-01 00:00:00.000
2004-05-28 00:00:00.000
2004-05-27 00:00:00.000
2004-05-26 00:00:00.000
2004-05-25 00:00:00.000
2004-05-24 00:00:00.000

However, I don't want any hours, minutes or seconds. So I tried this


select distinct(left(CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime), 11)) as PoolDate

Which does the insane. instead of returning this:

It now returns


Sep 30 2003
Sep 30 2002
Sep 29 2003
Sep 28 2002
Sep 27 2002
Sep 27 2000
Sep 26 2003
Sep 26 2002
Sep 25 2003
Sep 25 2002

Any other ideas?

I really hope Yukon gets their act together where dates are concerned cause this has been a pain in the neck for years I've had to work around.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-02 : 15:47:54

what do you want?

CONVERT(char(8), PoolArrivalDate, 112)
will return yyyymmdd
you can format it any way you want

select convert(char(9),convert(datetime,CONVERT(char(8), PoolArrivalDate, 112), 106)
from tbl
order by CONVERT(char(8), PoolArrivalDate, 112)
or
select convert(char(9), PoolArrivalDate, 106)
from tbl
order by CONVERT(char(8), PoolArrivalDate, 112)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-02 : 15:51:52

A couple of options.

datetime datatypes always return the hh:mm:ss portion. What I gave you removes the partial days giving you the date only which can be sorted correctly.

What nr suggested returns the date in a char() datatype 'yyyymmdd' which will sort correctly.

Question for you, why do you require no "time" portion of the date even if it is '00:00:00.000' which can be removed in many presentation layers and orders correctly?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-02 : 15:59:02

quote:
Originally posted by drymchaser

smalldatetime includes HH:MM no seconds.

CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime)

returns

mm/dd/yyyy 00:00:00.000

It seems that you are missing the point of the above post. The 00:00:00.000 simply means that there is no time value stored in the field, but the field is design to hold time. It will sort correctly so I am not sure what the fuss is about!?!

Also you say

quote:

I really hope Yukon gets their act together where dates are concerned cause this has been a pain in the neck for years I've had to work around.

Comparitively to some other languages, I think t-sql does very well with date manipulation. Atleast there isn't a Feb 31!

Corey Aldebol

--Sucks to be slow...

label
Posting Yak Master

197 Posts

Posted - 2004-06-02 : 16:43:36

quote:
Originally posted by nr

what do you want?

To return my dates in mm/dd/yyyy format, order desc.

quote:
select convert(char(9),convert(datetime,CONVERT(char(8), PoolArrivalDate, 112), 106)
from tbl
order by CONVERT(char(8), PoolArrivalDate, 112)
or
select convert(char(9), PoolArrivalDate, 106)
from tbl
order by CONVERT(char(8), PoolArrivalDate, 112)

Neither of those work. I'm trying to return a distinct list of poolarrivaldate's.

It throws an error when I try your suggestions:

The code modified to your suggestion:


select distinct(CAST(CONVERT(char(10), PoolArrivalDate, 112) as datetime)) as PoolDate
from aims.dbo.stock 
where PoolArrivalDate is not null
and PoolArrivalDate<>''
order by convert(smalldatetime, PoolArrivalDate, 112) desc

The error:


Error 145: Order by items must appear in the select list if the Select Distinct is specified.

Any more thoughts?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-02 : 18:07:23

Only that you might have defined the problem in the first place

select distinct(CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime)) as PoolDate
from aims.dbo.stock
where PoolArrivalDate is not null
and PoolArrivalDate<>''
order by CAST(CONVERT(char(8), PoolArrivalDate, 112) as datetime) desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources