By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,702 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,702 IT Pros & Developers. It's quick & easy.

Simple SQL Query (To Neglect time from DateTime DataType)

P: n/a

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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
>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.