Simple SQL Query (To Neglect time from DateTime DataType)

[kashifsulemani]

we have a table like this

OrderNo OrderDate
1 2005-11-04 01:12:47.000
2 2005-11-19 04:26:54.000
3 2005-11-16 11:03:23.000
4 2005-11-21 15:58:37.000
5 2005-11-24 21:45:04.000
what will be the sql query, so that the Result look like this.
only to neqlect the time factor from datetime data type .
OrderNo OrderDate
1 2005-11-04
2 2005-11-19
3 2005-11-16
4 2005-11-21
5 2005-11-24

[helmut woess]

Am 17 Nov 2005 12:34:35 -0800 schrieb ka************@hotmail.com:

we have a table like this

OrderNo OrderDate
1 2005-11-04 01:12:47.000
2 2005-11-19 04:26:54.000
3 2005-11-16 11:03:23.000
4 2005-11-21 15:58:37.000
5 2005-11-24 21:45:04.000
what will be the sql query, so that the Result look like this.
only to neqlect the time factor from datetime data type .
OrderNo OrderDate
1 2005-11-04
2 2005-11-19
3 2005-11-16
4 2005-11-21
5 2005-11-24

select orderno, convert(char(10),orderdate,120)

bye,
Helmut

[ZeldorBlat]

If you wanto remove the time portion and keep the value as datetime
(instead of char) you can use this:

CAST(FLOOR(CAST(someDate AS float)) AS datetime)

I always thought that was a nifty idiom -- but I'm not sure of the
performance implications (if any).

[Hugo Kornelis]

On 17 Nov 2005 14:52:41 -0800, ZeldorBlat wrote:

If you wanto remove the time portion and keep the value as datetime
(instead of char) you can use this:

CAST(FLOOR(CAST(someDate AS float)) AS datetime)

I always thought that was a nifty idiom -- but I'm not sure of the
performance implications (if any).

Hi ZeldorBlat,

This works, but it relies on the (undocumented, as far as I know)
conversion rules for datetime to float and float to datetime. Relying on
undocumented behaviour is allways dangerous.

Here's a safer way to trim the time from a date while still getting a
datetime result:

DATEADD(day, DATEDIFF(day, '20000101', someDate), '20000101')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[ZeldorBlat]

>Here's a safer way to trim the time from a date while still getting a

datetime result:

DATEADD(day, DATEDIFF(day, '20000101', someDate), '20000101')

Point taken. I like that one, too.