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

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 (

SQL DateTime, SQL Function, SQL Server, SQL String
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

Leave a Reply

Exit mobile version