none
casting DATEDIFF function as varchar

    Question

  • Hi ,

    I am writing one datediff function which I want to append with text (varchar) "R" as below.

    Means something like, 

    'R' + '' + DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME) as [datediff]   

    Can you please mention proper casting for this (so that resultant value should be in text)

    R-3

    R-2

    R-1...

    This is what I get datediff in negative numbers i.e. -3,-2,-1

    Next I also want to know, what if I get datediff as 0 (then output should be "R" and not R+0)

    And for positive values i.e. 1,2,3 how do I get R+1, R+2, R+3 etc...


    My final output should be

    R-2, R-1 (for negative datediffs)

    R (for 0 datediff)

    R+1, R+2 (for positive datediffs)

    Please note all datdiff in terms of DAYS only 

    Hope it is clear...Thanks to advise

    Wednesday, March 05, 2014 9:21 AM

Answers

  • Hi 

    you meant just use of DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME) at script level will do the need full ??

    I meant:

    Order by DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME) at script level.

    Or you can set the Sort order in Reporting services to DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME).

    Why does that not work?


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by UltraDev Thursday, March 06, 2014 12:07 PM
    Thursday, March 06, 2014 11:35 AM

All replies

  •                             
    ;WITH test AS 
    ( SELECT DATEADD(DAY, 1, CURRENT_TIMESTAMP) AS testdata, CURRENT_TIMESTAMP AS currentdate
    UNION 
    SELECT DATEADD(DAY, -1, CURRENT_TIMESTAMP) , CURRENT_TIMESTAMP
    UNION SELECT DATEADD(DAY, 2, CURRENT_TIMESTAMP) , CURRENT_TIMESTAMP
    UNION SELECT DATEADD(DAY, -2, CURRENT_TIMESTAMP) , CURRENT_TIMESTAMP
    )
    SELECT * 
    ,'R' + '' + CAST( DATEDIFF(Day,testdata, currentdate) AS NVARCHAR(200)) as [datediff]   
    FROM test


    sqldevelop.wordpress.com

    Wednesday, March 05, 2014 9:30 AM
  • UltraDev,

    check this:

    declare @tab table (date datetime,date2 datetime)
    insert @tab select getdate()-2,getdate()
    insert @tab select getdate()-1,getdate()
    insert @tab select getdate(),getdate()
    insert @tab select getdate()+1,getdate()
    insert @tab select getdate()+2,getdate()
    
    select 'R'+CASE WHEN diff>0 then '+' WHEN diff=0 THEN '' ELSE '-' END+CAST(abs(diff) as VARCHAR)
    from
    (
    select datediff(day,date2,date) as diff from @tab
    ) t


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, March 05, 2014 9:33 AM
  • You havent provided any table/data  structure.

    You can use CASE to achieve this,

    Declare @date date='05 mar 2014'
    
    select 'R' + 
    case when DATEDIFF(Day,@date, getdate())>0 then '+'+cast(DATEDIFF(Day,@date, getdate()) as varchar(100))
         when DATEDIFF(Day,@date, getdate())<0 then  cast(DATEDIFF(Day,@date, getdate()) as varchar(100))
         else ''
         end  as [datediff] 
    
    set  @date ='01 mar 2014'
    select 'R' + 
    case when DATEDIFF(Day,@date, getdate())>0 then '+'+cast(DATEDIFF(Day,@date, getdate()) as varchar(100))
         when DATEDIFF(Day,@date, getdate())<0 then  cast(DATEDIFF(Day,@date, getdate()) as varchar(100))
         else ''
         end  as [datediff] 
    set  @date ='01 apr 2014'
    select 'R' + 
    case when DATEDIFF(Day,@date, getdate())>0 then '+'+cast(DATEDIFF(Day,@date, getdate()) as varchar(100))
         when DATEDIFF(Day,@date, getdate())<0 then  cast(DATEDIFF(Day,@date, getdate()) as varchar(100))
         else ''
         end  as [datediff] 
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, March 05, 2014 9:34 AM
  • 'R' + CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') as [datediff]   


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Wednesday, March 05, 2014 9:38 AM
    • Marked as answer by UltraDev Wednesday, March 05, 2014 10:30 AM
    • Unmarked as answer by UltraDev Thursday, March 06, 2014 6:25 AM
    • Marked as answer by UltraDev Thursday, March 06, 2014 12:09 PM
    • Unmarked as answer by UltraDev Tuesday, March 11, 2014 6:31 AM
    Wednesday, March 05, 2014 9:36 AM
  • This is perfect Visakh16 !!!

    Thanks

    'R' + CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') as [datediff] 

    • Marked as answer by UltraDev Wednesday, March 05, 2014 10:30 AM
    • Unmarked as answer by UltraDev Thursday, March 06, 2014 6:25 AM
    • Marked as answer by UltraDev Thursday, March 06, 2014 12:09 PM
    • Unmarked as answer by UltraDev Tuesday, March 11, 2014 6:31 AM
    Wednesday, March 05, 2014 10:30 AM
  • you're welcome

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 05, 2014 12:04 PM
  • Hi Visakh16,

    One relevant thing to this, 

    although  i am getting desired output with this, but when i bring this data field [datediff] to my SSRS matrix report, it is not sorting it properly. i.e. ideally it should sort like,

    R-3,R-2,R-1,R,R+1,R+2,R+3 etc.

    But currently it is sorting like R-3,R-2,R-1,R+1,R+2,R

    (FYI, currently on matrix filter i am already doing sorting on this field as DESCENDING)

    it is working fine if i have only negatives and R (R-2,R-1,R)

    But if R+1,R+2 comes then it will not properly sort. 

    Any idea on this part ?

    Thursday, March 06, 2014 6:29 AM
  • Try the below:

    create Table T1(SYS_TRNH_REQUESTED_DATE date,SYS_TRNH_COMPLETION_DATETIME date)
    Insert into T1 Select getdate(),GETDATE()
    Insert into T1 Select getdate()-1,GETDATE()
    Insert into T1 Select getdate()-2,GETDATE()
    Insert into T1 Select getdate()+1,GETDATE()
    Insert into T1 Select getdate()+2,GETDATE()
    
    Select 'R'+CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') as [datediff]
    From t1
    Order by
    CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '0' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') desc
         
    Drop table T1


    Thursday, March 06, 2014 6:46 AM
  • i think this sorting is not required at sql level.

    in matrix report it should proper sorting..

    Thursday, March 06, 2014 6:57 AM
  • i think this sorting is not required at sql level.

    in matrix report it should proper sorting..

    Perfect solution if that possible.
    Thursday, March 06, 2014 6:58 AM
  • i am already doing sorting at sql level..but while transposing it to matrix report it is not considering it.

    what "order by" you have given i already tried earlier but it does not work out that way. I am getting output matrix report like below screen shot :

    

    Thursday, March 06, 2014 7:07 AM
  • Yep. for sorting just sort on parts seperately ie prefix part first ie R (if all have same value you can ignore it) followed by integer part provided by datediff without ABS.

    ie

    DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, March 06, 2014 7:08 AM
  • Hi Visakh,

    So at  script level now i am doing ORDER BY as :

    CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '0' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') desc

    While, at the same time I have removed sorting from my matrix report.

    As a result, for many scenarios it is sorting correctly now. But still some parts it is wrong. For example, below screen shot :

    Thursday, March 06, 2014 8:55 AM
  • any idea guys ?
    Thursday, March 06, 2014 9:31 AM
  • Why not sort by DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)?


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, March 06, 2014 9:48 AM
  • Hi 

    you meant just use of DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME) at script level will do the need full ??

    But is it not working out.

    I am using currently in script :

    ORDER BY
    CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '0' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') desc

    Please tell if it will give correct sorting or not because still i am getting in report display like below

    R

    R+3

    R+2

    R+1

    (it should be R, R+1, R+2, R+3)

    Thursday, March 06, 2014 11:01 AM
  • please help checking this sorting as it is really important for this SSRS matrix report

    ORDER BY

    CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME))>0 THEN '+' ELSE '0' END + COALESCE(CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME)),0) as varchar(10)),'') desc,

    SYS_TRNH_COMPLETION_DATETIME

    Thursday, March 06, 2014 11:15 AM
  • Hi 

    you meant just use of DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME) at script level will do the need full ??

    I meant:

    Order by DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME) at script level.

    Or you can set the Sort order in Reporting services to DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, SYS_TRNH_COMPLETION_DATETIME).

    Why does that not work?


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by UltraDev Thursday, March 06, 2014 12:07 PM
    Thursday, March 06, 2014 11:35 AM
  • yes got it. fine now thanks
    Thursday, March 06, 2014 11:44 AM
  • this worked out for me

    order by

    DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, isnull(SYS_TRNH_COMPLETION_DATETIME,getdate() ) )

    • Marked as answer by UltraDev Thursday, March 06, 2014 12:08 PM
    • Unmarked as answer by UltraDev Tuesday, March 11, 2014 6:31 AM
    Thursday, March 06, 2014 12:08 PM
  • HI Russel / Visakh,

    Although this below exp is giving me the result I want i.e. R-10, R-9, R-8...R-1,R,R+1,R+2,R+3...R+12,R+13...etc.

    But now I need a very small modification in this; means I want to restrict it to :

    R-1,  R,  R+1...R+6 (means from R-1 till R+6)

    and if it comes ...R-3,R-2 then it should be --> < (R-1)

    and if it comes R+7,R+8... then it should be --> >(R+6)

    Can you please advise ? Below is the one original I am already using...

    'R' + 
    CASE WHEN SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, isnull(SYS_TRNH_COMPLETION_DATETIME,getdate()) ))>0 THEN '+' ELSE '' END 
    + COALESCE
    (CAST(NULLIF(SIGN(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, isnull(SYS_TRNH_COMPLETION_DATETIME,getdate()) )) * ABS(DATEDIFF(Day,SYS_TRNH_REQUESTED_DATE, isnull(SYS_TRNH_COMPLETION_DATETIME,getdate()) )),0) as varchar(10)),'') as COL_R  

    To sum up, 

    To factor for cases where the bucket is R-2,R-3, etc or R+7,R+8 and so on then :
    "<(R-1)",  will take care of all records which are for orders completed before R-1 and

    ">(R+6)",  will take care of records which are for orders completed later than R+6.

    Hope it is clear....

    Thanks

    Tuesday, March 11, 2014 6:38 AM