CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
FartStopper
FartStopper

Reputation: 121

Extract Date And Hour Only From Date Time Field

I am running SQL Server 2008 and I have a datetime field, from this field I only want to extract the date and hour. I tried to parse out using datepart() but I think I did it incorrectly as when I attempted to string the two back together I got a number value. What I attempted was 

Select Datepart(YEAR, '12/30/2015 10:57:00.000') + 
       Datepart(HOUR, '12/30/2015 10:57:00.000')

And what was returned was 2025 What I want to see returned is

12/30/2015 10:00:00.000

Upvotes: 0

Views: 2541

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could use DATEADD and DATEDIFF:

DECLARE @d DATETIME = '12/30/2015 10:57:00.000';

SELECT DATEADD(second,DATEDIFF(second,'1970-01-01',@d)/3600*3600 , '1970-01-01')
-- 2015-12-30 10:00:00

LiveDemo

How it works:

  1. Get seconds difference from 1970-01-01
  2. Divide by 3600 (integer division so the part after decimal point will be skipped)
  3. Multiply by 3600 to get value back to full hours
  4. Add calculated seconds number to 1970-01-01

With SQL Server 2012+ the neat way is to use DATETIMEFROMPARTS:

SELECT DATETIMEFROMPARTS(YEAR(@d), MONTH(@d), DAY(@d), DATEPART(HOUR, @d),0,0,0)

LiveDemo2

Upvotes: 4

Related Questions