Case statement for the time section in a timestamp SQL

Question

All, I am trying to isolate the time portion only of a date/time stamp and then create a case statement to populate another column with data. This is how the date/time stamp looks: 2022-03-25 04:00:00

Here is the code I originally wrote but when I just use the hours portion I get an error in PowerBi saying invalid date/timestamp...

case when create_tmstp between '04:00:00' and '07:30:00' then 'N' else 'D' end as Day_Night,

Answer 1

From the looks of it, you're using SQL, not DAX. I say that, because you've tagged this with dax and not SQL.

Problem is that you're not showing us what create_tmsp is, or what version of what kind of sql server you're using. So, just to make it easier, this is an working example to use for instance inside SSMS. This uses getdate() to get the current datetime, and then converts it into just showing what hour it is (0-24), then there is a case when that checks which intervall it falls under. You can use this, and simply replace getdate() with the column you wish to use if you're using a server that accepts this syntax.

select 
DATEPART(HOUR,getdate()) AS 'HOUR', 
getdate() AS 'Datetime',
CASE WHEN DATEPART(HOUR,getdate()) BETWEEN 9 AND 16
    THEN 'Day'
WHEN DATEPART(HOUR,getdate()) BETWEEN 17 AND 24
    THEN 'Evening'
WHEN DATEPART(HOUR,getdate()) BETWEEN 0 AND 4
    THEN 'Night'
WHEN DATEPART(HOUR,getdate()) BETWEEN 5 AND 8
    THEN 'Morning'
END AS 'Label'

Example result:

Hour	Datetime	Label
0	2022-04-04T00:18:13.203Z	Night

Fiddle here: http://sqlfiddle.com/#!18/56f78/3829