473,230 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

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 12882
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Don Sealer | last post by:
I'm guessing this is pretty simple however not simple enough for me. I'm developing a database to track expenses, income, banking transactions, etc. I have a very simple query with four fields,...
0
by: vijayalakshmi.venkataraman | last post by:
Hello, I have a .NET client that accesses a Java webservice. Let me first say that the client stub I got generated from the wsdl was incomplete and had to make changes to it manually to make it...
5
by: jim Bob | last post by:
Hi, Can someone help with a simple query? I have a table with the following. Firstname Lastname InterviewerID1 InterviewerID2 InterviewerID3 ...
7
by: Ivan Marsh | last post by:
Hey Folks, I'm having a heck of a time wrapping mind around AJAX. Anyone know of a simple, straight-forward example for pulling a simple query from mysql with PHP using AJAX? As I...
24
by: rogynskyy | last post by:
Hi guys, I have to run a simple query on a MSSQL 2000. I'm new to database usage. I need to create a view with the following table columns LIST_PRICE decimal; MAP varchar(6);...
3
by: prabhas | last post by:
I want to swap two tuples in a table, using a single , simple query. No SELECT query allowed, no inner queries allowed. No PL/SQL allowed. Do you have any idea how to do this? e.g. my current...
3
by: rogynskyy | last post by:
Hi guys, I'm running MSDE 2000 A on Win XP I've got a database with several tables, all of the tables display data in query manager. I wrote this simple query: Select
9
by: muddasirmunir | last post by:
i have a simple query and does not getting desire results which i want i am using vb6 and access i had a table with with 8 fields but just to simplyfy by question i am just supposing to four. ...
1
by: Vajrala Narendra | last post by:
Hi am working with asp.net with backend Sql2000 i want to store NULL in datetime datatype field through coding. i wrote a update statement as Update table set Date1='NULL' where ........ but...
2
by: sillyr | last post by:
Hi - My database is using Access 2007. I created a simple query from two linked tables that sums up the data for all records for 11 different fields. Then I made a form that shows the totals for...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.