locked
convert access IIF into sql RRS feed

  • Question

  • I have this query in access and need to change it to sql. 

    IIf(DateAdd('n',+30,[dbo_Master]![DateEntered])<=[dbo_Master]![ArrivalDate]+[dbo_Master]![ArrivalTime], +1) AS PreRegistered


    Here is the whole query in case it helps.  

    SELECT [All ORg Data].[PLB Mgr Name], [All ORg Data].[Major Org Mgr Name], [All ORg Data].[Org Mgr Name], [All ORg Data].[Department Name], dbo_Master.HostName, dbo_Master.DateEntered, IIf(DateAdd('n',+30,[dbo_Master]![DateEntered])<=[dbo_Master]![ArrivalDate]+[dbo_Master]![ArrivalTime], +1) AS PreRegistered, IIf(DateAdd('n',+30,[dbo_Master]![DateEntered])>[dbo_Master]![ArrivalDate]+[dbo_Master]![ArrivalTime],+1 ) AS NotPreRegistered, dbo_Master.ArrivalDate, dbo_Master.ArrivalTime, dbo_Master.VisitorName, dbo_Master.VisitorCompany, dbo_Master.Department, dbo_Master.VisitorArrived, dbo_Master.VisitorType, dbo_VisitorDepart.DepartureDate, dbo_VisitorDepart.DepartureTime, dbo_VisitorDepart.[Exit Location] AS Expr1, IIf([dbo_Master]![VisitorArrived]=True,"Arrived") AS Arrived FROM (dbo_VisitorDepart RIGHT JOIN dbo_Master ON dbo_VisitorDepart.BarCode=dbo_Master.BarCode) LEFT JOIN [All ORg Data] ON dbo_Master.Department=[All ORg Data].[Department Code] WHERE (((dbo_Master.ArrivalDate) Between Forms!FrmPreRegisteredByPLB!StartDate And Forms!FrmPreRegisteredByPLB!EndDate) And ((dbo_Master.VisitorType)=Forms!FrmPreRegisteredByPLB!VisitorType)) ORDER BY [All ORg Data].[PLB Mgr Name], [All ORg Data].[Major Org Mgr Name], [All ORg Data].[Org Mgr Name], [All ORg Data].[Department Name], dbo_Master.HostName, dbo_Master.DateEntered, dbo_Master.ArrivalTime, dbo_Master.VisitorName;

    Any help would be greatly appreciated. Thanks.

    Monday, November 23, 2009 8:02 PM

Answers

  • The IIF in Access transforms into CASE WHEN statements in T-SQL. However, your IIF statement seems to only have 1 condition - I'm  that familiar with this shortened syntax.

    Anyway, in T-SQL the above statement would be

    cast (case when DateAdd(day,+30,[dbo_Master].[DateEntered])<=[dbo_Master].[ArrivalDate]) then 1 else 0 end ) as bit) AS PreRegistered
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, November 23, 2009 8:21 PM
  • Here is an example to show how you can compare days.

    DECLARE @t DATETIME, @a DATETIME
    SELECT @t = '10/12/2008'
    SELECT @a = '10/13/2008'
    
    SELECT CASE 
    WHEN DATEDIFF(dd,0,@t) <= DATEDIFF(dd,0,@a) THEN 'Yes'
    ELSE 'NO' END
    AS [Arrival Date on date registered]

    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by Kalman Toth Sunday, November 29, 2009 12:29 PM
    Monday, November 23, 2009 8:27 PM
  • So ArrivalDate and/or ArrivalTime are in character format - does the following query return the correct datetime?

    SELECT
    dbo_Master.ArrivalDate, dbo_Master.ArrivalTime,
    CAST(dbo_Master.ArrivalDate AS DATETIME) + CAST(dbo_Master.ArrivalTime AS DATETIME)
    

    Dave

    • Proposed as answer by Justanewone Monday, November 23, 2009 11:06 PM
    • Marked as answer by Kalman Toth Sunday, November 29, 2009 12:30 PM
    Monday, November 23, 2009 10:10 PM

All replies

  • I have this query in access and need to change it to sql. 

    IIf(DateAdd('n',+30,[dbo_Master]![DateEntered])<=[dbo_Master]![ArrivalDate]+[dbo_Master]![ArrivalTime], +1) AS PreRegistered


    You know what it does?

    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 8:06 PM
  • This is a report that checks for a visitor that has arrived prior or on the same date they have been pre-registered for. Does that help? or need further information.
    Monday, November 23, 2009 8:11 PM
  • How is DateRegistered saved in your  table?


    Abdallah, PMP, ITIL, MCTS
    Monday, November 23, 2009 8:15 PM
  • dateregistered is saved as

    dateentered (datetime, NULL)
    Monday, November 23, 2009 8:21 PM
  • The IIF in Access transforms into CASE WHEN statements in T-SQL. However, your IIF statement seems to only have 1 condition - I'm  that familiar with this shortened syntax.

    Anyway, in T-SQL the above statement would be

    cast (case when DateAdd(day,+30,[dbo_Master].[DateEntered])<=[dbo_Master].[ArrivalDate]) then 1 else 0 end ) as bit) AS PreRegistered
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, November 23, 2009 8:21 PM
  • Here is an example to show how you can compare days.

    DECLARE @t DATETIME, @a DATETIME
    SELECT @t = '10/12/2008'
    SELECT @a = '10/13/2008'
    
    SELECT CASE 
    WHEN DATEDIFF(dd,0,@t) <= DATEDIFF(dd,0,@a) THEN 'Yes'
    ELSE 'NO' END
    AS [Arrival Date on date registered]

    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by Kalman Toth Sunday, November 29, 2009 12:29 PM
    Monday, November 23, 2009 8:27 PM
  • Has your MSAccess data been transferred to an SQL Server or are you accessing the MDB file from Management Studio or similar?

    As previously said you need a CASE Statement, but I am concerned about the fact the date and time are in seperate columns - which is why I asked the question above. The literal translation would be:
    SELECT CASE WHEN DATEADD(MINUTE, 30, dbo_Master.DateEntered) <= dbo_Master.ArrivalDate + dbo_Master.ArrivalTime THEN 1 ELSE 0 END AS 'PreRegistered'
    FROM dbo_Master
    
    Dependant on how the ArrivalDate and ArrivalTime formats are presented you may need to convert them to the SQL type of DATETIME.

    Dave
    Monday, November 23, 2009 9:36 PM
  • yes you are right. i am getting an error message of "conversion failed when converting datetime from character string."

    And no i it has not been transferred to an SQL server.
    Monday, November 23, 2009 9:44 PM
  • So ArrivalDate and/or ArrivalTime are in character format - does the following query return the correct datetime?

    SELECT
    dbo_Master.ArrivalDate, dbo_Master.ArrivalTime,
    CAST(dbo_Master.ArrivalDate AS DATETIME) + CAST(dbo_Master.ArrivalTime AS DATETIME)
    

    Dave

    • Proposed as answer by Justanewone Monday, November 23, 2009 11:06 PM
    • Marked as answer by Kalman Toth Sunday, November 29, 2009 12:30 PM
    Monday, November 23, 2009 10:10 PM
  • no it does not return the correct datetime. both master.arrivaldate, and master.arrivaltime are in datetime format. here is what they look like

     Arrivaldate                                     Arrivaltime
    9/8/2009 12:00:00 AM    12/30/1899 10:15:19 AM
    Monday, November 23, 2009 10:29 PM
  • i am sorry dave, it worked. I was my mistake i was plugging something wrong in there.
    Monday, November 23, 2009 10:38 PM
  • thank you all!
    Monday, November 23, 2009 10:38 PM
  • Your Welcome
    Monday, November 23, 2009 11:06 PM