getting only date from datetime

Question

getting only date from datetime

muthukrishnan.e

SSC Veteran

Points: 294
More actions
May 15, 2008 at 3:40 am

#93310

I have a table XYZ with a datetime field.
In this field the date and time is stored: for example:
datetime
-----------------------
2008-05-15 12:59:36.000
2008-05-15 12:55:09.000
2008-05-15 00:00:00.000
2008-05-15 00:00:00.000
I want to get the count of this datetime field with a where condition: for example
select count(datetime) from XYZ where datetime='2008-05-15'
The result is 2, but the actual result should be 4.
this is because they differ with time also, but for me when i give only date condition i should get count as 4 irrespective of time.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

As you've noticed SQL Server datetime types include the time as well. So you'll have to take this into account. Try this:

select count(datetime) from XYZ where datetime between '2008-05-15 00:00:00' and '2008-05-15 23:59:59.999'

Note that I've added milliseconds to the right-hand side of the between clause. Strictly speaking you don't need to add the time to the left-hand side because it will default to 00:00:00. But I prefer to be explicit in such cases.

Karl
source control for SQL Server

Sanaullah SSC Eights! Points: 892 More actions · Answer 2

Hi,

If u want to get the count of date only the use this

SELECT COUNT(1) FROM XYZ WHERE CONVERT(VARCHAR(10),DATETIME,101)='2002-05-15'

I think this will work.

Thanks

Sanaullah.

SQLZ SSChampion Points: 12872 More actions · Answer 3

Sanaullah (5/15/2008)
Hi,
If u want to get the count of date only the use this
SELECT COUNT(1) FROM XYZ WHERE CONVERT(VARCHAR(10),DATETIME,101)='2002-05-15'
I think this will work.
Thanks
Sanaullah.

That will work. However, it's not ideal because you're using the CONVERT function in the WHERE clause, which will always result in a table scan. This may or may not matter to you in this situation but I thought I'd bring it up so you were aware.

Karl
source control for SQL Server

Mauve SSChampion Points: 11316 More actions · Answer 4

SQLZ (5/15/2008)
As you've noticed SQL Server datetime types include the time as well. So you'll have to take this into account. Try this:
select count(datetime) from XYZ where datetime between '2008-05-15 00:00:00' and '2008-05-15 23:59:59.999'
Note that I've added milliseconds to the right-hand side of the between clause.

Using "999" for milliseconds (in SQL Server) is wrong! Note that the milliseconds in SQL Server are 300 thousands of a second with rounded values of .000, .003, or .007 seconds. From the BOL:

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

Therefore, in your example the end date is actually '2008-05-16 00:00:00.000' which will yield incorrect results!

If this was a financial stock trading system, this small oversight would have major ramifications.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 5

Don't use Between for date ranges. Use:

Where date >= '5/16/2008' and date < '5/17/2008'

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Greg Jennings SSC Enthusiast Points: 175 More actions · Answer 6

JohnG,

Just for clarification, are you saying that a date/time value 1 millisecond before midnight and a date/time value 1 millisecond after midnight would have the same value in a "datetime" column?

Mauve SSChampion Points: 11316 More actions · Answer 7

Greg Jennings (5/16/2008)
JohnG,
Just for clarification, are you saying that a date/time value 1 millisecond before midnight and a date/time value 1 millisecond after midnight would have the same value in a "datetime" column?

Yes. Example using ISO 8601 imputs (see BOL):

SELECT

CONVERT(datetime, '2008-05-16T23:59:59.999', 126) AS BeforeMidnight,

CONVERT(datetime, '2008-05-17T00:00:00.001', 126) AS AfterMidnight

The result is:

[font="Courier New"]

BeforeMidnight AfterMidnight

----------------------- -----------------------

2008-05-17 00:00:00.000 2008-05-17 00:00:00.000[/font]

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Mauve SSChampion Points: 11316 More actions · Answer 8

To avoid adding more re: date accuracy, please refer to the following, and currently active, thread around getdate() accuracy:

http://www.sqlservercentral.com/Forums/Topic500971-338-1.aspx

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 9

It would appear that a couple folks didn't test their code... and with disasterous results for the OP...

Here's some code that demonstrates the points that JohnG and Gus were trying to get across... read the comments for an explanation... 🙂

[font="Courier New"]--===== Create a table and data to demo/test with

CREATE TABLE #TestTable

(RowNum INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,

MyDate DATETIME)

INSERT INTO #TestTable (MyDate)

SELECT '2008-05-15 12:59:36.000' UNION ALL

SELECT '2008-05-15 12:55:09.000' UNION ALL

SELECT '2008-05-15 00:00:00.000' UNION ALL

SELECT '2008-05-16 12:59:36.000' UNION ALL

SELECT '2008-05-16 12:55:09.000' UNION ALL

SELECT '2008-05-16 00:00:00.000' UNION ALL

SELECT '2008-05-16 00:00:00.000'

--===== Create an index to demo with

CREATE INDEX IX_TestTable_MyDate

ON #TestTable (MyDate)

--===== Code gives the WRONG answer because of the 3.3ms rounding

-- of 23:59:59.999

PRINT 'Code gives the WRONG answer because of the 3.3ms rounding'

SELECT *

FROM #TestTable

WHERE MyDate BETWEEN '2008-05-15 00:00:00' AND '2008-05-15 23:59:59.999'

--===== Won't give any answer because the date literal is the wrong format

PRINT 'Won''t give any answer because the date literal is the wrong format'

SELECT *

FROM #TestTable

WHERE CONVERT(VARCHAR(10),MyDate,101)='2008-05-15'

--===== Gives the correct answer but can't use an index properly.

-- Will only do an Index SCAN, no chance of Index SEEK.

-- Should be corrected to use ISO date literal, as well

PRINT 'Gives the correct answer but can''t use an index properly.'

SELECT *

FROM #TestTable

WHERE CONVERT(VARCHAR(10),MyDate,101)='05/15/2008'

--===== Gives the correct answer but can't use an index properly.

-- Will only do an Index SCAN, no chance of Index SEEK.

-- But we did follow the ISO rules 😉

PRINT 'Gives the correct answer but can''t use an index properly.'

SELECT *

FROM #TestTable

WHERE CONVERT(VARCHAR(10),MyDate,112)='20080515'

--===== Code give the RIGHT answer and can use an index

-- AND follows the ISO rules

PRINT 'Code give the RIGHT answer and can use an index' SELECT *

FROM #TestTable

WHERE MyDate >= '20080515'

AND MyDate < '20080516'

--===== Housekeeping

DROP TABLE #TestTable

[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 10

wait till MSSQL2008 🙂

(don't get angry on me, I think a lot of people had the same question)

Wilfred
The best things in life are the simple things

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 11

Wilfred van Dijk (5/17/2008)
wait till MSSQL2008 🙂
(don't get angry on me, I think a lot of people had the same question)

Heh... I, on the other hand, will loath the day when the DATE and TIME datatypes of 2k8 finally hit the street for real... then I'll have to answer a bunch of questions about how to combine the two and still be able to use an index. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 12

Wilfred van Dijk (5/17/2008)
wait till MSSQL2008 🙂
(don't get angry on me, I think a lot of people had the same question)

Seriously, folks, the separate date and time fields in SQL 2008 aren't all that exciting. You can already achieve the same end result by storing the date part of DateTime as an integer, and the same for the time part.

SQL Server stores DateTime data as 2 4-byte integers. It's not hard to separate them.

select cast(getdate() as int),

floor((cast(getdate() as decimal(16,8))-cast(getdate() as int))*power(10,8))

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (5/17/2008)
Wilfred van Dijk (5/17/2008)
wait till MSSQL2008 🙂
(don't get angry on me, I think a lot of people had the same question)
Heh... I, on the other hand, will loath the day when the DATE and TIME datatypes of 2k8 finally hit the street for real... then I'll have to answer a bunch of questions about how to combine the two and still be able to use an index. 😉

Never mind the questions about why the "results don't match" (since TIME has a higher precision than DATETIME)....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

gaurava16fc SSC Enthusiast Points: 118 More actions · Answer 14

select count(datetime) from XYZ where convert(datetime,left((convert(nvarchar,datetime),11)) = '2008-05-15'