datetime => how to have complete date with 00:00:00.000

Question

Post reply

datetime => how to have complete date with 00:00:00.000

bernardchristophe

Old Hand

Points: 366
More actions
December 2, 2009 at 1:49 am

#218178

Hi,
i work with sql server 2005 and i 'm a beginner
I have a table with a column datetime.
in this table i have some date as this :
2008-10-28 11:04:04.207
2009-03-30 13:26:10.433
2009-01-16 14:06:20.033
but i'd like when i do a query select * from myTable to have the date in format
2008-10-28 00:00:00.000
2009-03-30 00:00:00.000
2009-01-16 00:00:00.000
How i can do this !?
Thanks in advance
Christophe

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

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

Mike_D Hall of Fame Points: 3295 More actions · Answer 1

A number of ways of doing this:

SELECTCONVERT(datetime,CONVERT(char(8),MyDateTimeColumn,112))

FROM MyTable

or

SELECTCONVERT(datetime,DATEDIFF(DD,0,MyDateTimeColumn))

FROM MyTable

Hope that helps.

Mike

Simon Liddle SSCrazy Points: 2580 More actions · Answer 2

One way I often do this:

CREATE TABLE #MyTable

(

MyDate DATETIME

)

INSERT INTO #MyTable

SELECT '2008-10-28 11:04:04.207' UNION ALL

SELECT '2009-03-30 13:26:10.433' UNION ALL

SELECT '2009-01-16 14:06:20.033'

SELECT MyDate,

DATEADD(dd, DATEDIFF(dd, 0, MyDate), 0) MyDate_DateOnly

FROM #MyTable

DROP TABLE #MyTable

Hope this helps.

Cheers,

Simon 🙂

bernardchristophe Old Hand Points: 366 More actions · Answer 3

Hi both,

thanks for your sample, it's ok now !

thanks for your time

christophe

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Here is a link to some common date routines:

http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

CEWII

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Be a bit careful about using character conversions of dates, folks. They're s-l-o-w. Might not make any difference with just a couple of thousand rows but if you work with millions of rows, it can really add up in a hurry.

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Jeff, what do you suggest as a best-practice for returning a date with 00:00:00.000?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Either of the following is what I consider to be a "best practice". They're both very fast but the first one edges out the second when it comes to millions of rows if you need to apply it to something that large.

SELECT CAST(DATEDIFF(dd,0,GETDATE()) AS DATETIME),

DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

Replace the GETDATE() with whatever date you may have including dates in a column from a table. Just don't forget to add the FROM clause for the table. 😀

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

nigel. SSChampion Points: 11636 More actions · Answer 8

You may find this useful too:

http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/[/url]

Nigel
How to post questions

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

Jeff Moden (4/7/2011)
Either of the following is what I consider to be a "best practice". They're both very fast but the first one edges out the second when it comes to millions of rows if you need to apply it to something that large.
SELECT CAST(DATEDIFF(dd,0,GETDATE()) AS DATETIME),
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Replace the GETDATE() with whatever date you may have including dates in a column from a table. Just don't forget to add the FROM clause for the table. 😀

Ah... I just had a horrible thought based on how the original question was posed. If you're trying to use such a thing as criteria for a JOIN or a WHERE clause, DO NOT use either of the two methods above on the COLUMN of a TABLE. Instead, do this...

SELECT something

FROM SomeTable

WHERE SomeDateColumn >= SomeWholeDate

AND SomeDateColumn < DATEADD(dd,1,SomeWholeDate)

The code above finds all the rows in SomeTable that have a date (with or without time) that falls on SomeWholeDate and it does so while still allowing an index on SomeDateColumn to be used. Using just about any formula on SomeDateColumn itself will prevent the use of 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)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 10

But wouldn't that defeat the purpose of getting a date like "2011-04-08 00:00:00.000" in the first place?

SELECT DATEADD(dd,0,GETDATE()) results in "2011-04-08 09:17:05.080"

Consider I have a developer who writes this:

WHERE ([NextDateTime] between CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 00:00:00.000'))

and CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 23:59:59.999'))) and [Disabled] = 0

What they are trying to do is grab the data that fits these 2 date and times...surely there's a better way to do so (they use this type of CONVERTing and so forth to make JOINS and use it in WHERE clauses all over the place).

What is the most efficient way to accomplish this?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

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

MyDoggieJessie (4/8/2011)
But wouldn't that defeat the purpose of getting a date like "2011-04-08 00:00:00.000" in the first place?
SELECT DATEADD(dd,0,GETDATE()) results in "2011-04-08 09:17:05.080"[/code]

~~I never suggested that code or anything like it. Go back and look again.~~

{edit} Ah... sorry. Didn't know what you were driving at. You're correct. The trouble is I thought you were working with WHOLE dates already.

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 12

MyDoggieJessie (4/8/2011)
Consider I have a developer who writes this:
WHERE ([NextDateTime] between CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 00:00:00.000'))
and CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 23:59:59.999'))) and [Disabled] = 0
What they are trying to do is grab the data that fits these 2 date and times...surely there's a better way to do so (they use this type of CONVERTing and so forth to make JOINS and use it in WHERE clauses all over the place).
What is the most efficient way to accomplish this?

First, the method that is written is actually incorrect because 23:59:59.999 will round up to the NEXT day at midnight.

Also, as you may have guessed, the conversion of the dates to VARCHAR is pretty slow to being with. Add concatenation into the mix and you get a fair bit slower.

One of the two fastest ways to rewrite the WHERE clause you posted is as follows...

WHERE NextDateTime >= CAST(DATEDIFF(dd,0 ,GETDATE()) AS DATETIME) --Today starting at midnight

AND NextDateTime < CAST(DATEDIFF(dd,-1,GETDATE()) AS DATETIME) --Tomorrow starting at midnight

AND [Disabled] = 0

NEVER use BETWEEN on dates with times.

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 13

NEVER use BETWEEN on dates with times.

Why?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Kalink SSC Rookie Points: 38 More actions · Answer 14

Using between to compare datetimes is a didatic concept and this practice isn't optimazed.

May this article help you.

http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html