-1

I'm tring to extract hour values from a datetime:

I want all the datetimes like '2021-10-27 04:55:00.000' to show the hour data, so: '2021-10-27 04:00:00.000'

What query do i run to get this?

Thanks in advance!

Denmark123
  • 3
  • 1
  • 1
    Please add sample data, expected result and what you have tried – Jens Aug 12 '22 at 10:05
  • 1
    `dateadd(hour, datediff(hour, 0, datetimecol), 0)` – Squirrel Aug 12 '22 at 10:10
  • This requirement is described too unclear. You should provide more sample data and expected outcome, thus allowing to understand your whole idea. Beside further questions, you did as example not explain whether your times will be delivered as 24 hours format or if you have to do further action in order to get different results for 4am or 4pm. – Jonas Metzler Aug 12 '22 at 10:15

4 Answers4

3

Use date maths and a "magic" date:

DATEADD(HOUR,DATEDIFF(HOUR,0,YourColumn),0);

This gets the number of hours between the "date" 0 (1900-01-01) and your date value, and then adds that many hours to the "date" 0.

On SQL Server 2022 (currently in preview), however, you have access to DATETRUNC and DATE_BUCKET that make this much easier:

DATETRUNC(HOUR,YourColumn),
DATE_BUCKET(HOUR,0,YourColumn)
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

The current time (UK) is 11:06 (am) and:

select format(getdate(), 'yyyy-MM-dd HH')

..returns 2022-08-12 11

You can add whatever you want on the end of it, e.g.:

select format(getdate(), 'yyyy-MM-dd HH') + ':00:00'

..which gives 2022-08-12 11:00:00

NB Format is a SQL-Server function. I'm not sure how many other databases have it.

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • 3
    Also note `FORMAT()` is [awful](https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format) for performance – HoneyBadger Aug 12 '22 at 10:14
  • @HoneyBadger True. But any date formatting in SQL instead of client GUI code is really bad design, so it's a race to see which will have a worse effect. However, if doing a one-off query I have no problem with it. – simon at rcl Aug 12 '22 at 10:17
0

For the string representation of a date and time value you can do something like this

SELECT CONCAT(FORMAT(dt, 'yyyy-MM-dd HH'),'00:00.000')

see Custom formatting and Custom Date and Time Formatting

AlanK
  • 1,827
  • 13
  • 16
0

use convert

declare @a datetime='2021-10-27 04:55:00.000'
select convert(varchar(10),@a,120) + ' '+convert(varchar(2), datepart(hour,@a))+':00:00'
RF1991
  • 2,037
  • 4
  • 8
  • 17