Search code examples
sqldatetimeparametersssrs-2008

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

Solution

  • 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'