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. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Get Datetime Without Time
From Wiki
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
- DECLARE @d DATETIME
- SET @d = '2007-11-07 14:30:35.370'
- 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:
- SELECT CONVERT(DATETIME, DATEDIFF(dd, 0, @d)) -- convert could be less costly than dateadd
- 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)
- SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
Output: 2007-11-07 00:00:00.000
CONVERT Fixed date
- DECLARE @d DATETIME
- SELECT @d = '2007-11-07 14:30:35.370'
- SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),@d ,112))
- --If the value is assigned to a datetime data type then you can skip the convert to datetime part
- SELECT @d = CONVERT(VARCHAR(8),@d ,112)
- SELECT @d
Output: Both 2007-11-07 00:00:00.000
Today (2007-11-07)
- 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
- DECLARE @d DATETIME
- SELECT @d = '2007-11-07 14:30:35.370'
- 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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.