Search code examples
t-sqlbusiness-intelligence

IIF Condition Ques

Can someone explain this condition, because I'm getting wrong Time data eg : I'm expecting sch departure time as 15.10 but I'm getting 15.01

[Sch Dep Time] = IIF(DATEPART(Hour,[Journey and Details.schdeptime]) < 10 AND DATEPART(Minute,[Journey and Details.schdeptime]) < 10,
                 ('0' + DATEPART(Hour,[Journey and Details.schdeptime]) + ':0' + DATEPART(Minute,[Journey and Details.schdeptime])),
                     IIF(DATEPART(Hour,[Journey and Details.schdeptime]) < 10,
                         ('0' + DATEPART(Hour,[Journey and Details.schdeptime]) + ':' + DATEPART(Minute,[Journey and Details.schdeptime])),
                         (DATEPART(Hour,[Journey and Details.schdeptime]) + ':0' + DATEPART(Minute,[Journey and Details.schdeptime]))))
Solution

  • Update

    It suddenly hit me that [Sch Dep Time] should contain the time component of the dataTime value stored in [Journey and Details.schdeptime], in a minute resolution. For that, you don't need to mess around with specific date parts and string concatenation, all you have to do is use convert:

    [Sch Dep Time] = CONVERT(char(5), [Journey and Details.schdeptime], 108)

    The 108 style returns hh:mm:ss (24 hours), and by using char(5) you are just taking the first 5 chars of that string - hh:mm.

    First version

    You have overcomplicated things. Try using the old right('00' + val, 2) trick instead:

    [Sch Dep Time] = RIGHT('00' + CAST(DATEPART(Hour,[Journey and Details.schdeptime]) AS VARCHAR(2)), 2) + ':' +
                 RIGHT('00' + CAST(DATEPART(Minute,[Journey and Details.schdeptime]) AS VARCHAR(2)), 2)

    Exlpanation:

    You start off by concatenating leading zeroes to the string you want.
    Suppose you have a string representing a number that must always have 4 digits, but it might be 1234 or 0003 - so you start by doing '0000' + @YourNumber.
    Then, you use RIGHT to trim off any unwanted zeros - suppose you now have 000023, but you want 0023 - you do RIGHT('000023', 4) to get the last 4 chars.