468,243 Members | 1,924 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,243 developers. It's quick & easy.

Simple SQL Query (To Neglect time from DateTime DataType)


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

Nov 23 '05 #1
4 12640
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
Nov 23 '05 #2
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).

Nov 23 '05 #3
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)
Nov 23 '05 #4
>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.

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Don Sealer | last post: by
5 posts views Thread by jim Bob | last post: by
7 posts views Thread by Ivan Marsh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.