Post reply

subtracting a case statement result from a Convert(char(8) result

  • March 14, 2016 at 1:50 pm

    #316949

    Hello,

    I am struggling to get a case statement result to be subtracted from another result in the same format.I want to be able to subtract the result of this:

    CASE WHEN QueueDate IS NULL THEN convert(char(8), StartDate,108) ELSE convert(char(8),QueueDate ,108) END

    from this:

    CONVERT(char(8), EndDate, 108)

    but I can't get it to work. I'm struggling as I'm writing freehand SQL in VB and it just says automation error.

    Please note, StartDate, QueueDate and EndDate are all DateTime2 fields and I need the result to return hh:mm:ss.

    Any help would be greatly appreciated.

  • March 14, 2016 at 1:53 pm

    #1865052

    don't change data types.

    if you are using dates, use DATEADD and DATEDIFF functions. don't convert them to strings, or any other data type.

    something like this returns the minutes in difference(if you use seconds, there's a max diff of ~71 years before a datatype overflow)

    you can then format the minutes to whatever you want.

    SELECT

    CASE

    WHEN QueueDate IS NULL

    THEN DATEDIFF(mi,StartDate,EndDate)

    ELSE DATEDIFF(mi,QueueDate,EndDate)

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2016 at 2:09 pm

    #1865057

    Hi Lowell, thanks for your help, this runs without error and returns the result in minutes. I modified it so it returns seconds for what I need. However, I was hoping to have the result in hh:mm:ss format, is that at all possible?

    Thanks again.

    EDIT: there would be a maximum of 15 mins between each of the three DateTime2 fields and never crosses into the next day. Not sure if that adds anything.

  • March 14, 2016 at 2:20 pm

    #1865058

    Why use a CASE statement?

    SELECT DATEDIFF(ss,ISNULL(QueueDate,StartDate),EndDate)

    For duration, here's a formula using date/time calculations.

    DECLARE @StartDT DATETIME2

    ,@EndDT DATETIME2

    ;

    SELECT @StartDT = '2000-01-01 10:30:50.780'

    ,@EndDT = '2000-01-02 12:34:56.789'

    ;

    --===== Display the dates and the desired format for duration

    SELECT StartDT = @StartDT

    ,EndDT = @EndDT

    ,Duration = STUFF(CONVERT(VARCHAR(20),DATEADD( ss, DATEDIFF( ss, @StartDT, @EndDT), 0),114),1,2,DATEDIFF(hh,@StartDT, @EndDT))

    It's partially explained in here: http://www.sqlservercentral.com/articles/T-SQL/103343/

    You just need to join both of these options, which you shouldn't have a problem if you understand them. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2016 at 2:27 pm

    #1865061

    That format is pretty straight forward if you have the diff in seconds.

    SELECT CAST( (DATEDIFF(second, '3/14/2016', getdate()) / 60) / 60 AS varchar) + ':' + CAST( (DATEDIFF(second, '3/14/2016', getdate()) / 60) % 60 AS varchar) + ':' + CAST(DATEDIFF(second, '3/14/2016', getdate()) % 60 AS varchar)

  • March 14, 2016 at 2:31 pm

    #1865062

    jimtimber (3/14/2016)

    Hi Lowell, thanks for your help, this runs without error and returns the result in minutes. I modified it so it returns seconds for what I need. However, I was hoping to have the result in hh:mm:ss format, is that at all possible?

    Thanks again.

    EDIT: there would be a maximum of 15 mins between each of the three DateTime2 fields and never crosses into the next day. Not sure if that adds anything.

    Didn't see this before my reply, the formula could be simpler if the duration is less than 24 hours.

    Duration = CONVERT(VARCHAR(20),DATEADD( ss, DATEDIFF( ss, @StartDT, @EndDT), 0),114)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2016 at 2:37 pm

    #1865063

    ZZartin (3/14/2016)

    That format is pretty straight forward if you have the diff in seconds.

    SELECT CAST( (DATEDIFF(second, '3/14/2016', getdate()) / 60) / 60 AS varchar) + ':' + CAST( (DATEDIFF(second, '3/14/2016', getdate()) / 60) % 60 AS varchar) + ':' + CAST(DATEDIFF(second, '3/14/2016', getdate()) % 60 AS varchar)

    Too long:-P

    Also, you're not defining length for your varchars, that's a bad practice that can hurt at the less expected moment.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2016 at 2:53 pm

    #1865068

    Ah.. yeah true, didn't see that the dates would always be the same day.

  • March 14, 2016 at 2:54 pm

    #1865069

    Ah, this is a lot more complex than I'd have hoped. Most important for the result, I have to have the hh:mm:ss format.

    ZZmartin, I tried your suggestion, it said automation error. (I'm writing freehand in SQL so can't see what is falling over). I checked the code in Notepad++ but couldn't see the issue.

    Luis Cazares, the SELECT DATEDIFF(ss,ISNULL(QueueDate,StartDate),EndDate) part is much cleaner than my code and works fine, thank you.

    However, I really need it in hh:mm:ss and I've got stuck on the "declare" part of your second but of code ( I have used it in SQL Server Management Studio before but I'm not sure this can be done when trying to run SQL via VB?).

  • March 14, 2016 at 3:17 pm

    #1865074

    The method Luis posted should work if you'll never have differences more than 24 hours.

    This should work to get the format you want.

    SELECT RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, CASE WHEN QueueDate IS NULL THEN StartDate ELSE QueueDate END, EndDate), 0), 120), 8)

  • March 14, 2016 at 3:29 pm

    #1865076

    Maybe?:

    SELECT CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, ISNULL(QueueDate, StartDate), EndDate), 0), 8)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 14, 2016 at 3:52 pm

    #1865078

    I'm not sure what to do with the DECLARE part of it. I have added the:

    DECLARE @StartTime DATETIME2, @EndTime DATETIME2

    at the beginning of my statement and then added to the SELECT statement:

    RIGHT(CONVERT(varchar(30), DATEADD(s,DATEDIFF(s,CASE WHEN QueueDate IS NULL THEN @StartTime ELSE QueueDate END, @EndTime),0),120),8)

    and in the WHERE clause:

    WHERE @StartTime=StartDate AND @EndTime=EndDate

    It runs without error but doesn't output anything at all. I'm lost!! 🙂

  • March 14, 2016 at 3:55 pm

    #1865080

    Same thing, just shorter

    SELECT RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, ISNULL(QueueDate,StartDate), EndDate), 0), 120), 8)

  • March 14, 2016 at 3:57 pm

    #1865081

    Same thing, just shorter

    SELECT RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, ISNULL(QueueDate,StartDate), EndDate), 0), 120), 8)

  • March 14, 2016 at 4:14 pm

    #1865084

    jimtimber (3/14/2016)

    I'm not sure what to do with the DECLARE part of it. I have added the:

    DECLARE @StartTime DATETIME2, @EndTime DATETIME2

    at the beginning of my statement and then added to the SELECT statement:

    RIGHT(CONVERT(varchar(30), DATEADD(s,DATEDIFF(s,CASE WHEN QueueDate IS NULL THEN @StartTime ELSE QueueDate END, @EndTime),0),120),8)

    and in the WHERE clause:

    WHERE @StartTime=StartDate AND @EndTime=EndDate

    It runs without error but doesn't output anything at all. I'm lost!! 🙂

    You shouldn't need the declare part or the where clause at all. What that's doing is declaring a variable which will be NULL then the where clause is never returning any results since it's comparing a NULL variable to the fields.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply