How to Trim TIME Part in DATETIME Values? – Interview Question of the Week #200

Question: How to Trim TIME Part in DATETIME Values?

Answer: One of my blog followers sent me an email asking this question

How to Trim TIME Part in DATETIME Values? - Interview Question of the Week #200 datetrim

I want to convert ‘2013-01-15 11:23:49.930′ into output =’2013-01-15 11:00:00.000’

If you look at the output, you can clearly understand that only hour part along with date value should be retained and minutes and seconds should be truncated.

Well. They may be several methods to do this. A simple method is as shown below

SET @mydate='2013-01-15 11:23:49.930'
SELECT DATEADD(hour ,DATEDIFF(hour,0,@mydate),0) AS date_with_hour_only

The result is

2013-01-15 11:00:00.000

The idea is to trim the time part to HOUR only. DATEDIFF(hour,0,@mydate) will only consider the HOUR difference since 1900-01-01 to given date and time and when this value is added back to 1900-01-01 using DATEADD function, it gives you the given date and time with considering only HOUR part along with DATE value

Let me know if you have any other methods.

Here are the previous three interview questions and answers.

On a separate note, I am very happy that this is 200th interview question and answer.

Reference: Pinal Dave (

, , ,
Previous Post
Does Sort Order in Index Column Matters for Performance? – Interview Question of the Week #199
Next Post
How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201

Related Posts

5 Comments. Leave new

  • Select Cast(Convert(varchar(13),Getdate(),120)+’:00:00.000′ as Datetime)

  • Wilfred van Dijk
    November 19, 2018 1:05 pm

    Or this: select cast(cast(getdate() as date) as datetime)

    • Hi Wilfred,

      Your SELECT won’t give the anticipated output. It always gives the time as 12 AM (i.e. 00 AS hour), but as per Pinal, you need to retain the hour and suppress the minutes, seconds and milliseconds.


  • SELECT FORMAT(@mydate,’yyyy-MM-dd HH:00:00.000′) AS date_with_hour_only

  • select convert(datetime,convert(varchar(13),getdate(),120)+ ‘:00’)


Leave a Reply