How to Floor an SQL DateTime

by thebeebs 3. July 2008 06:10

If you call GetDate you get todays date and time but what if you just want the date and not the time.

SQL provides no simple mechanism to do this but the solution below is probably the best way I’ve found:

CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME)

So a full example would be:

DECLARE @CurrentDate Datetime SET @CurrentDate = GETDATE() DECLARE @CurrentDateFloor Datetime SET @CurrentDateFloor  = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME) SELECT @CurrentDateFloor

2007–30-07 12:12:45 becomes 2007-30-07 00:00:00

553eb4e1-d50d-4e8d-9a52-15c3be15bd2f|1|2.0

Tags:

So you’ve hard coded VAT at 17.5% in SQL? Yikes!… Alistair Campbell has just announced VAT has been reduced from 17.5% to...An XML User And Role Provider for ASP.netIf you want to implement User and Roles in your ASP.net Application but don’t have an SQL serv...How to create a mystery missile trail with HTML5On Monday the 8th of November a large condensation trail was visible in the skies of California. It ...

Comments (4) -

Tomislav

8/13/2008 5:37:31 AM #

select cast(convert(varchar,getdate(),106) as datetime)

nishant

2/11/2009 10:01:19 PM #

insted of this you can use
select convert(datetime,convert(int, getdate())) as date
it is more easy

thebeebs

2/12/2009 4:20:19 AM #

@nishant, That won't work, if you run it with a date with a time past 12:00 then it will add a day on to the result date:

Morining Test:
DECLARE @Testdate datetime
SET @Testdate = convert(datetime, '03/June/1982 00:00:00')
Select @Testdate
select convert(datetime,convert(int, @Testdate)) as date
RESULT 1: 1982-06-03 00:00:00.000
RESULT 2: 1982-06-03 00:00:00.000

Afternoon Test:
DECLARE @Testdate datetime
SET @Testdate = convert(datetime, '03/June/1982 13:00:00')
Select @Testdate
select convert(datetime,convert(int, @Testdate)) as date
RESULT 1: 1982-06-03 13:00:00.000
RESULT 2: 1982-06-04 00:00:00.000

nishant

2/12/2009 4:42:43 AM #

Thank a lot

Comments are closed