DateTime to time interval in T-Sql

Question

I am working on an SSRS report and I need to display hourly data count in a table. The hourly interval need to be displayed as 4:00pm - 5:00pm I have a log table which has DateTime available with each transaction. This table refreshes daily. I am grouping on the hour component of this timestamp to get the hourly count. Is there any option available in SQL/SSRS for converting hourly value to 04:00pm- 05:00pm format?

Converting the count, or the column name? It's doable in both, but i'm sure you know how to change the header of a tablix in SSRS. Can you elaborate a bit? — S3S, Sep 29 '16 at 16:13
I tried Right(100+datepart(hour, getdate()), 2), but it gives me values from 0 to 23 — viky, Sep 29 '16 at 16:15
And there is no way to CONVERT a datetime to the value you want? — dfundako, Sep 29 '16 at 16:20
dfundako: Not that I know! @scsimon: I have SQL DateTime value and I want to convert the hour component to 1:00pm format in SSRS or Sql — viky, Sep 29 '16 at 17:22

Abhay Chauhan · Answer 1 · 2016-09-30T03:21:55.930

Use case statement:

SELECT CASE
           WHEN DATEPART(hour,TransactionDateTime) = 0 THEN '12:00am-01:00am'
           WHEN DATEPART(hour,TransactionDateTime) = 1 THEN '01:00am-02:00am' ...
       END FROM your_table

Dynamic way:

    SELECT CASE
    WHEN DATEPART(hour,TransactionDateTime) >12 THEN cast (DATEPART(hour,TransactionDateTime) -12 as VARCHAR (2)) + ':00pm-' +cast(DATEPART(hour,TransactionDateTime) -11 as VARCHAR (2)) + ':00pm' 
    WHEN DATEPART(hour,TransactionDateTime) <12
    cast (DATEPART(hour,TransactionDateTime) as VARCHAR (2)) + ':00am-' +cast(DATEPART(hour,TransactionDateTime)+1 as VARCHAR (2)) + ':00am' 
 WHEN DATEPART(hour,TransactionDateTime) =12
    cast (DATEPART(hour,TransactionDateTime) as VARCHAR (2)) +':00pm-1:00pm'
    END
    FROM your_table

For corner case (11pm) handle am/pm using a nested case statement. check the datepart and return am/pm based on the results. I was not able to test the query since I am posting this from my smartphone.

Hope this helps

Unnikrishnan R · Answer 2 · 2016-09-30T05:43:40.607

0

Use the below script to convert datetime to 12 hour format.

  SELECT  LEFT (STUFF(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,YourColumn,100),7)),7),6,0,''),2)+':00'+RIGHT (LTRIM(RIGHT(CONVERT(VARCHAR(20), YourColumn, 100), 7)),2)
    +' - '+LEFT (STUFF(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar, DATEADD(hh, 1, YourColumn),100),7)),7),6,0,''),2)+':00'+RIGHT (LTRIM(RIGHT(CONVERT(VARCHAR(20), DATEADD(hh, 1, YourColumn), 100), 7)),2)
  FROM YourTable

Sample output :

edited Sep 30 '16 at 05:43

answered Sep 29 '16 at 16:39

Unnikrishnan R

4,965
1
12
21

It still gives me minute component, I need 00 for minute – viky Sep 29 '16 at 17:18

score 0 · Answer 3 · answered Sep 29 '16 at 20:13

I would recommend that you simply create a simple scalar function in SQL Server for this one. See following example:

CREATE FUNCTION DateTimeToHourInterval
(
    @currentDateTime DATETIME
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @nextHourDateTime DATETIME = DATEADD(HOUR, 1, @currentDateTime);

    DECLARE @intervalStart VARCHAR(10) = REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(50), DATEADD(MINUTE, -1* DATEPART(MINUTE, @currentDateTime), @currentDateTime), 100), 7)), 'PM', ' PM'), 'AM', ' AM');

    DECLARE @intervalEnd VARCHAR(10) = REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(50), DATEADD(MINUTE, -1* DATEPART(MINUTE, @nextHourDateTime), @nextHourDateTime), 100), 7)), 'PM', ' PM'), 'AM', ' AM');

    RETURN @intervalStart + ' - ' + @intervalEnd

END

The function can now be used as follow:

SELECT dbo.DateTimeToHourInterval(GETDATE());

DateTime to time interval in T-Sql

3 Answers3