Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 17 down vote favorite
6

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0

share|improve this question

5 Answers

up vote 24 down vote accepted

The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Two different and excellent answers on StackOverflow bear this out: One, Two

Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.

This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions

This technique is also extendable.

  • yesterday: DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
  • start of month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
  • end of last month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
  • start of next month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Edit:

As I mentioned about determinism, varchar methods are not safe unless you use style 112.

Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.

The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.

share|improve this answer
nice answer - I will up vote your answer when I get enough rep. – Piers Myers Feb 24 '11 at 20:00
+1 I'm using select cast (floor(cast (GETDATE() as float)) as datetime), but it is listed in one of your links – bernd_k Feb 24 '11 at 20:29
If done against a column this wouldn't perform any better in SQL 2005 than my code which you complained about. Any function call made against a column in SQL 2005 would cause a table (or index) scan. – mrdenny♦ Feb 24 '11 at 22:45
@mrdenny: yes of course, but for output or as a computed column it makes a huge difference... – gbn Feb 25 '11 at 5:30
up vote 4 down vote

You have to convert it to varchar specifying a format pattern (110 in this case) then convert (or cast) back to datetime.

select getdate(), cast(convert(varchar(10), getdate(), 110) as datetime)
share|improve this answer
I suppose it would be better to use the 106 date format as it has the 3 letter month so that it won't get confused with the US and UK dates having the day/month the other way around. – Piers Myers Feb 24 '11 at 18:37
1  
@Piers Myers: a varchar conversion is the worst way to do it – gbn Feb 24 '11 at 19:47
@Piers the 110 syntax doesn't matter display wise. You'll be converting it back to datetime so the client application would then control the date display. – mrdenny♦ Feb 24 '11 at 22:42
@gbn I wouldn't ever do this against a column. If I needed a column, something like WHERE column BETWEEN select getdate(), cast(convert(varchar(10), getdate(), 110) as datetime) and select getdate(), cast(convert(varchar(10), dateadd(dd, 1, getdate()), 110) as datetime) would work just fine. – mrdenny♦ Feb 24 '11 at 22:44
So you never just SELECT the date only? Or had ever used GROUP BY on date only? Or never considered a computed column? Or never considered language and set dateformat? – gbn Feb 25 '11 at 5:54
show 1 more comment
up vote 0 down vote

select CONVERT(varchar, TransactionDate, 101) as 'TransactionDate' from transactiondetails

table name: transactiondetails column name: TransactionDate

share|improve this answer
up vote -4 down vote

select getdate(), cast(getdate() as date)

share|improve this answer
1  
Nope, the date datatype was introduced in SQL Server 2008 R1. – mrdenny♦ Feb 24 '11 at 18:01
up vote -4 down vote

You can also try:

SELECT CAST(GETDATE() AS date) AS Current_Date;
share|improve this answer
1  
The Date datatype is not available in SQL Server 2005 – Max Vernon Oct 24 '12 at 2:35
1  
-1 The same wrong answer as already suggested. – Paul White Oct 24 '12 at 10:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.