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

Am 17 Nov 2005 12:34:35 -0800 schrieb kashifsulemani@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

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).

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)

>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.