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,651 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.