Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Get Datetime Without Time

From Wiki

Jump to: navigation, search

There are a couple of ways to display a datetime value without time First a couple of ways which display zeros for the time part of datetime

DATEADD + DATEDIFF Fixed date

  1. DECLARE @d datetime
  2. SET @d = '2007-11-07 14:30:35.370'
  3. SELECT DATEADD(dd, DATEDIFF(dd, 0, @d), 0)

Note that the DateAdd is performing the function of converting the number returned by DateDiff back into a date. In many cases, this conversion can be implicit, or you can explicitly do it another way as well:

  1. SELECT Convert(datetime, DateDiff(dd, 0, @d)) -- convert could be less costly than dateadd
  2. SET @d = DateDiff(dd, 0, @d) -- implicit conversion from int because @d is datetime

Output: 2007-11-07 00:00:00.000

Today (2007-11-07)

  1. SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

Output: 2007-11-07 00:00:00.000

CONVERT Fixed date

  1. DECLARE @d datetime
  2. SELECT @d = '2007-11-07 14:30:35.370'
  3.  
  4. SELECT CONVERT(datetime,CONVERT(varchar(8),@d ,112))
  5.  
  6. --If the value is assigned to a datetime data type then you can skip the convert to datetime part
  7. SELECT @d = CONVERT(varchar(8),@d ,112)
  8. SELECT @d

Output: Both 2007-11-07 00:00:00.000


Today (2007-11-07)

  1. SELECT CONVERT(datetime,CONVERT(varchar(8),GETDATE() ,112))

Output: 2007-11-07 00:00:00.000


Displaying the date without time at all, not even zeros

  1. DECLARE @d datetime
  2. SELECT @d = '2007-11-07 14:30:35.370'
  3.  
  4. SELECT CONVERT(varchar(10),@d ,120),CONVERT(varchar(8),@d ,112)

Output: 2007-11-07, 20071107


Contributed by: --SQLDenis 16:54, 30 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Dates

122 Rating: 2.5/5 (104 votes cast)