Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jay83091
Starting Member
14 Posts |
 Posted - 2015-01-06 : 00:59:00
|
| Hi Experts,I am trying to create a new column by grouping the LossDateTime (timestamp format. e.g. "2013-09-09 06:40:00.24") column with the logic below.Could you please help?Thank you.If LossDateTime is greater than 06:00 and less than 12:01 then '06:01 to 12:00'If LossDateTime is greater than 12:00 and less than 18:01 then '12:01 to 18:00'If LossDateTime is greater than 18:00 and less than 24:01 then'18:01 to 24:00' |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2015-01-06 : 07:11:49
|
| Use a case something like this:Change the getdate() with your column name. select [LossDateTime] = case when datepart(hour, getdate()) > 6 And datepart(hour, getdate()) <= 12 Then '06:01 to 12:00' when datepart(hour, getdate()) > 12 And datepart(hour, getdate()) <= 18 Then '12:01 to 18:00' When datepart(hour, getdate()) > 18 And datepart(hour, getdate()) <= 24 Then '18:01 to 24:00' Else '00:00' EndWe are the creators of our own reality! |
 |
|
|
Saroj
Starting Member
1 Post |
Posted - 2015-01-06 : 07:26:49
|
| SELECTCASE WHEN LEFT(CONVERT(TIME,GETDATE()),5) > CONVERT(TIME,'6:00') AND LEFT(CONVERT(TIME,GETDATE()),5) < CONVERT(TIME,'12:01') THEN '06:01 to 12:00' WHEN LEFT(CONVERT(TIME,GETDATE()),5) > CONVERT(TIME,'12:00') AND LEFT(CONVERT(TIME,GETDATE()),5) < CONVERT(TIME,'18:01') THEN '12:01 to 18:00' WHEN LEFT(CONVERT(TIME,GETDATE()),5) > CONVERT(TIME,'18:00') AND LEFT(CONVERT(TIME,GETDATE()),5) < CONVERT(TIME,'24:01') THEN '18:01 to 24:00' ELSE '' END AS Timerange |
|
|
|
|
|
|
|
|
Time Grouping?