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
DECLARE @mydate DATETIME 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.
- Does Sort Order in Index Column Matters for Performance? – Interview Question of the Week #199
- How to Find Size of the Index for Tables? – Interview Question of the Week #198
- What is Read Ahead Read in SQL Server? – Interview Question of the Week #197
On a separate note, I am very happy that this is 200th interview question and answer.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Select Cast(Convert(varchar(13),Getdate(),120)+’:00:00.000′ as Datetime)
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.
Thanks,
Srini
SELECT FORMAT(@mydate,’yyyy-MM-dd HH:00:00.000′) AS date_with_hour_only
select convert(datetime,convert(varchar(13),getdate(),120)+ ‘:00’)