CODEWITHSUNDEEP
sqldatetimeparametersssrs-2008
Justin Harris
Justin Harris

Reputation: 27

SQL Datetime with time intervals

I would like to display the following: 23 Jan 06:00 - 23 Jan 18:00 and also 23 Jan 18:00 - 24 Jan 06:00.

I'm not sure how I would achieve this but if anything could assist please. I need it to basically display today's date in the format above. I am trying to do this for a parameter in ssrs but writing it out in sql.This is for 12 hour intervals.

Look forward to any responses. Thanks

Upvotes: 0

Views: 309

Answers (3)

NiveaGM
NiveaGM

Reputation: 259

Please find the SQL code below

  1. If you want 12 hours from getdate() time now then you can use the below code 

    SELECT CONVERT(VARCHAR(24), GETDATE(), 113)+' - '+ 
CONVERT(VARCHAR(24),DATEADD(HOUR,12,getdate()),113)`

    the result will be like 25 Oct 2017 11:54:19:393 - 25 Oct 2017 23:54:19:393

  2. If you want to display getdate() with year from 06:00 to 18:00 and next 12 hours from 18:00 to 6:00 then you can use the below code

    SELECT CONVERT(VARCHAR(24), cast(GETDATE() as DATE), 113)+' 06:00 - '+ 
CONVERT(VARCHAR(24),cast(getdate() as date),113)+' 18:00'  
union all   
SELECT CONVERT(VARCHAR(24), cast(GETDATE() as DATE), 113)+' 18:00 - '+ 
CONVERT(VARCHAR(24),cast(DATEADD(HOUR,24,getdate()) as DATE),113)+' 06:00'

  3. If you want to display getdate() without year from 06:00 to 18:00 and next 12 hours from 18:00 to 6:00 then you can use the below code

    SELECT LEFT(CONVERT(VARCHAR(24), cast(GETDATE() as DATE), 113),6)+' 06:00 - '+ 
LEFT(CONVERT(VARCHAR(24),cast(getdate() as date),113),6)+' 18:00'   
union all  
SELECT LEFT(CONVERT(VARCHAR(24), cast(GETDATE() as DATE), 113),6)+' 18:00 - '+ 
LEFT(CONVERT(VARCHAR(24),cast(DATEADD(HOUR,24,getdate()) as DATE),113),6)+' 06:00'

Upvotes: 2

Zorkolot
Zorkolot

Reputation: 2027

One method is to populate a dataset using a parameter. I'll explain how to do this.

In the Report Data, add a parameter to the report. I've named it @Date in my example. Set it's datatype to Date/Time. If you want today's date then set it's default to NOW().

Add a Dataset to the report, name it whatever you like. Add the following query to the dataset:

SELECT CAST(DAY(@Date) as varchar) --current day
       + ' ' + CONVERT(varchar(3), @Date, 100) --current month
       + ' 06:00 - ' 
       + CAST(DAY(@Date) as varchar) --still current day
       + ' ' + CONVERT(varchar(3), @Date, 100) --still current month
       + ' 18:00' AS [Period1]

      ,CAST(DAY(@Date) as varchar) --still current day
       + ' ' + CONVERT(varchar(3), @Date, 100) 
       + ' 18:00 - ' 
       + CAST(DAY(DATEADD(DAY, 1, @Date)) as varchar) --usually the next day + 1 but could be first day of next month
       + ' ' + CONVERT(varchar(3), DATEADD(DAY, 1, @Date), 100) --usually the same month but could be next month 
                                                                --if start day was last day of month
       + ' 06:00' AS [Period2]

What this code does is take the day and month name for the parameter. It concatenates as a string, adding spaces, time, and dashes. When calculating the 06:00 take into account the day number and month name- this is because the start date may be the last day of the month... hence the day number resets to 1 and the month name changes to the next month. I use DATEADD which adds +1 day to the parameter date so this is handled by the function.

The values are stored in [Period1] and [Period2] of the dataset.

Upvotes: 0

Pete Rennard-Cumming
Pete Rennard-Cumming

Reputation: 1618

Check the hour of your date. If it's below 6, display the yesterday evening/this morning slot. If it's between 6 and 17, display today's 06:00-18:00 slot. If it's 18 or over, display the evening/tomorrow morning slot.

In Oracle SQL, the code looks like this:

SELECT
CASE WHEN To_Char(SYSDATE, 'hh24') < 6 THEN
  To_Char(SYSDATE-1, 'dd Mon') || ' 18:00 - ' || To_Char(SYSDATE, 'dd Mon') || ' 06:00'
  WHEN To_Char(SYSDATE, 'hh24') BETWEEN 6 AND 17 THEN
  To_Char(SYSDATE, 'dd Mon') || ' 06:00 - ' || To_Char(SYSDATE, 'dd Mon') || ' 18:00'
  WHEN To_Char(SYSDATE, 'hh24') >= 18 THEN
  To_Char(SYSDATE, 'dd Mon') || ' 18:00 - ' || To_Char(SYSDATE+1, 'dd Mon') || ' 06:00'
  ELSE 'Error?' END time_interval
FROM DUAL

This logic should also work in SQL Server or an SSRS expression, with slightly different syntax.

Upvotes: 0

Related Questions