nvachar Data type conversations to datetime

s imam 21 Reputation points
2023-11-02T22:48:22.6366667+00:00

I need help in converting the Access SQL Script to SQL Script. Any help will be appreciated.

[STATUE] data type is nvarchar 'eg:20250510'

  1. IIF(DateAdd("m",0, [STATUTE]) - Date() > 540, "Yes", "No") AS Stat2005,
  2. (Year([STATUTE]) - Year(Date())) * 12 + Month([STATUTE]) - Month(Date()) AS St,
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,451 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 92,801 Reputation points
    2023-11-02T22:57:56.3+00:00

    I don't know Access, so I don't know what the first script is achieving, but if it is computing a difference in days, that would be

    IIF (datediff(DAY, sysdatetime(), STATUTE) > 540, 'Yes', 'No') AS Stat2005

    The second could be

    datediff(MONTH, sysdatetime(), STATUTE) AS St.

    To note:

    • The parameters to datediff come in reverse order to normal subtraction. That is the earlier date/time come first.
    • datediff returns the number of boundaries passed. datediff(YEAR, '20230101', '20241231') and datediff(YEAR, '20231231', '20240101') both return 1. But I think the Access expression behaves the same.