none
Access SQL to SQL sever Syntax help RRS feed

  • Question

  • Hi,

    Is anyone able to assist in converting the following SQL syntax in Access into the correct SQL server syntax please? Apologies that there are a few lines of code that I need help with. I am new to Access and SQL server so very difficult until I attend my course in really being able to understand the correct functions needed to replicate. 

    1) DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in])),1) AS Date_From, 

    2)DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in]))+IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)),1)-1 AS Date_To, 

    3)DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in]))+IIf(Round((DateValue([Date_Required])-DateValue(DateSerial(Year([Date_in]),Month([Date_in]),1)))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue(DateSerial(Year([Date_in]),Month([Date_in]),1)))/(365/12),0)),1)-1 AS Date_To_new, 

    4)DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in]))+IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)),1)-1 AS Date_To_old, 

    5) Year([Date_From]) & Format(Month([Date_From]),"00") AS Period_from, 

    6) IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)) AS Months, 

    7) Year([Date_To]+1) & Format(Month([Date_To]+1),"00") AS Renewal_Period, 



    • Edited by AJ_25 Monday, July 16, 2018 8:45 AM
    Monday, July 16, 2018 8:41 AM

Answers

  • I guess the error is due to an invalid value coming as the second argument to datefromparts function. So you have to examine your data and check whether there is possibility of an overflow for the month argument. 

    For Example, the statement 

    select datefromparts(2018, 14, 1)

    yields the same error you posted. It is because the month field is passed as 14 which invalid

    Similar issue should be happening in your case. So you have to examine your data and make necessary adjustments in the query..

    Monday, July 16, 2018 4:53 PM
  • Take a look at DATEADD\DATEDIFF functions

    Select DateAdd(Year, DateDiff(Year, 0, GetDate()) - 1, 0)
    
    Select DateAdd(Year, -1, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) - 1)

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms186724(v=sql.100)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, July 16, 2018 8:50 AM
    Moderator
  • just replace with corresponding SQL Server function 

    for examples 1 can be written like



    DATEFROMPARTS(YEAR(Date_In),MONTH(date_In),1)
    
    or
    
    CAST(DATEADD(mm,DATEDIFF(mm,0,Date_In),0) AS date)
    

    DateSerial will provde imilar functionality as DATEFROMPARTS

    Similarly DateValue can be replced by CONVERT with appropriate style code


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 8:58 AM
    Moderator
  • Thanks for your help. 

    I have tried to make the changes here and get the correct syntax but keep getting an error messgae, any idea why please?

    Dateadd(Year(convert([Date_in])),Month(convert([Date_in]))+IIf(Round((convert([Date_Required])-convert([Date_in]))/(365/12),0)=0,1,Round((convert([Date_Required])-convert([Date_in]))/(365/12),0)),1)-1 AS Date_To

    Incorrect syntax near ')'.

    CONVERT syntax is wrong

    it should be

    convert(datetime,[Date_in],<your style code>)

    style code depends on what format your date values come for example if its British then 103 etc

    Other parts look fine assuming you're using SQL 2012 or above


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 9:24 AM
    Moderator
  • Sorry, once I get one to work the rest should be easy to follow but still getting an error message on incorrect syntax


    Dateadd(Year(convert(datetime([Date_in]))),Month(convert(datetime([Date_in])))+IIf(Round((convert(datetime([Date_Required])-convert(datetime([Date_in]))/(365/12),0))=0,1,Round((convert(datetime([Date_Required]))))-convert(datetime([Date_in])))/(365/12),0)),1)-1 AS Date_To 

    dateadd synatx is wrong

    Why not you refer syntax of various functions first before you use them?

    For example, a simple search on DATEADD will give you this

    https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017

    see the syntax and you will notice that its like this

    DATEADD(datepart,number,date)

    datepart will yyyy for year, mm for month etc.

    for more details refer the documentation


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 10:56 AM
    Moderator
  • There were some logic/syntax issues in the previous queries...pls check the corrected versions below:

    select datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0) end, 1)) AS Date_To_new
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To_old
    
    select cast(year([Date_From]) as varchar(10)) + RIGHT('0' + RTRIM(cast(MONTH([Date_From]) as varchar(10))), 2) AS Period_from
    
    select case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end AS Months
    
    select cast(year(dateadd(day, 1, [Date_To])) as varchar(10)) + RIGHT('0' + RTRIM(cast(month(dateadd(day, 1, [Date_To])) as varchar(10))), 2) AS Renewal_Period

    Thank you, I am still getting an error

    Cannot construct data type date, some of the arguments have values which are not valid.

    Any idea how I resolve this please? I have tried to resolve by using this piece of code but that errors also

    datefromparts(datepart(year([Date_in]), datepart(month([Date_in]), 1) AS Date_From,

    this is again wrong

    why did you add datepart?

    I already gave you the suggestion for this

    datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From,

    is enough


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 1:24 PM
    Moderator

All replies

  • Take a look at DATEADD\DATEDIFF functions

    Select DateAdd(Year, DateDiff(Year, 0, GetDate()) - 1, 0)
    
    Select DateAdd(Year, -1, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) - 1)

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms186724(v=sql.100)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, July 16, 2018 8:50 AM
    Moderator
  • just replace with corresponding SQL Server function 

    for examples 1 can be written like



    DATEFROMPARTS(YEAR(Date_In),MONTH(date_In),1)
    
    or
    
    CAST(DATEADD(mm,DATEDIFF(mm,0,Date_In),0) AS date)
    

    DateSerial will provde imilar functionality as DATEFROMPARTS

    Similarly DateValue can be replced by CONVERT with appropriate style code


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 8:58 AM
    Moderator
  • Thanks for your help. 

    I have tried to make the changes here and get the correct syntax but keep getting an error messgae, any idea why please?

    Dateadd(Year(convert([Date_in])),Month(convert([Date_in]))+IIf(Round((convert([Date_Required])-convert([Date_in]))/(365/12),0)=0,1,Round((convert([Date_Required])-convert([Date_in]))/(365/12),0)),1)-1 AS Date_To

    Incorrect syntax near ')'.

    Monday, July 16, 2018 9:14 AM
  • Thanks for your help. 

    I have tried to make the changes here and get the correct syntax but keep getting an error messgae, any idea why please?

    Dateadd(Year(convert([Date_in])),Month(convert([Date_in]))+IIf(Round((convert([Date_Required])-convert([Date_in]))/(365/12),0)=0,1,Round((convert([Date_Required])-convert([Date_in]))/(365/12),0)),1)-1 AS Date_To

    Incorrect syntax near ')'.

    CONVERT syntax is wrong

    it should be

    convert(datetime,[Date_in],<your style code>)

    style code depends on what format your date values come for example if its British then 103 etc

    Other parts look fine assuming you're using SQL 2012 or above


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 9:24 AM
    Moderator
  • Sorry, once I get one to work the rest should be easy to follow but still getting an error message on incorrect syntax


    Dateadd(Year(convert(datetime([Date_in]))),Month(convert(datetime([Date_in])))+IIf(Round((convert(datetime([Date_Required])-convert(datetime([Date_in]))/(365/12),0))=0,1,Round((convert(datetime([Date_Required]))))-convert(datetime([Date_in])))/(365/12),0)),1)-1 AS Date_To 

    Monday, July 16, 2018 9:31 AM
  • Check below queries..go through the queries thoroughly and check whether I have missed anything..

    select datefromparts(year(Date_in), month(Date_in), 1) AS Date_From
    
    select datefromparts(year(Date_in), month(Date_in) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else dateadd(day, -1, round((datediff(day, [Date_Required],[Date_in]))/(365/12),0))) AS Date_To
    
    select datefromparts(year(Date_in), month(Date_in) + case when round((datediff(day, [Date_Required],datefromparts(year(Date_in), month(Date_in), 1)))/(365/12),0)=0 then 1 else dateadd(day, -1, round((datediff(day, [Date_Required],datefromparts(year(Date_in), month(Date_in), 1)))/(365/12),0))) AS Date_To_new
    
    select datefromparts(year(Date_in), month(Date_in) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else dateadd(day, -1, round((datediff(day, [Date_Required],[Date_in]))/(365/12),0))) AS Date_To_old
    
    select cast(year([Date_From]) as varchar(10)) + RIGHT('0' + RTRIM(cast(MONTH([Date_From]) as varchar(10))), 2) AS Period_from
    
    select case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) AS Months
    
    select cast(year(dateadd(day, 1, [Date_To])) as varchar(10)) + RIGHT('0' + RTRIM(cast(month(dateadd(day, 1, [Date_To])) as varchar(10))), 2) AS Renewal_Period
    Monday, July 16, 2018 10:45 AM
  • Sorry, once I get one to work the rest should be easy to follow but still getting an error message on incorrect syntax


    Dateadd(Year(convert(datetime([Date_in]))),Month(convert(datetime([Date_in])))+IIf(Round((convert(datetime([Date_Required])-convert(datetime([Date_in]))/(365/12),0))=0,1,Round((convert(datetime([Date_Required]))))-convert(datetime([Date_in])))/(365/12),0)),1)-1 AS Date_To 

    dateadd synatx is wrong

    Why not you refer syntax of various functions first before you use them?

    For example, a simple search on DATEADD will give you this

    https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017

    see the syntax and you will notice that its like this

    DATEADD(datepart,number,date)

    datepart will yyyy for year, mm for month etc.

    for more details refer the documentation


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 10:56 AM
    Moderator
  • Thank you for your help with converting the code, really appreciated. 

    I do however get this error message on the 2nd logic 

    datefromparts(year(Date_in), month(Date_in) + 
    case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 
    else dateadd(day, -1, round((datediff(day, [Date_Required],[Date_in]))/(365/12),0))) as Date_To,

    Incorrect syntax near ')'.

    I thought it was an extra bracket near AS but removing that didn't resolve. 

    Monday, July 16, 2018 11:20 AM
  • Thank you for your help with converting the code, really appreciated. 

    I do however get this error message on the 2nd logic 

    datefromparts(year(Date_in), month(Date_in) + 
    case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 
    else dateadd(day, -1, round((datediff(day, [Date_Required],[Date_in]))/(365/12),0))) as Date_To,

    Incorrect syntax near ')'.

    I thought it was an extra bracket near AS but removing that didn't resolve. 

    The above expression doesnt make any sense

    can you explain in words what you're trying to achieve here?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 11:32 AM
    Moderator
  • Not sure, I am new to Access and I have been tasked with moving everything from Access onto SQL server so it can feed into a data visualisation tool. The person who created this in Access is no longer here 
    Monday, July 16, 2018 11:36 AM
  • Not sure, I am new to Access and I have been tasked with moving everything from Access onto SQL server so it can feed into a data visualisation tool. The person who created this in Access is no longer here 

    What do you mean?

    Without knowing what you want how will you ensure the expression you're coming up is correct or not? Assuming the expression works fine

    And how will anyone else be able to tell you what you're trying to get?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Monday, July 16, 2018 11:37 AM
    Moderator
  • Access works currently, that's how the reporting is done but we want to move away from using Access. When I run an SQL Server query I am able to cross reference the output to Access and once it matches back, I know that SQL server query is working as expected
    • Edited by AJ_25 Monday, July 16, 2018 11:48 AM
    Monday, July 16, 2018 11:43 AM
  • There were some logic/syntax issues in the previous queries...pls check the corrected versions below:

    select datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0) end, 1)) AS Date_To_new
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To_old
    
    select cast(year([Date_From]) as varchar(10)) + RIGHT('0' + RTRIM(cast(MONTH([Date_From]) as varchar(10))), 2) AS Period_from
    
    select case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end AS Months
    
    select cast(year(dateadd(day, 1, [Date_To])) as varchar(10)) + RIGHT('0' + RTRIM(cast(month(dateadd(day, 1, [Date_To])) as varchar(10))), 2) AS Renewal_Period
    Monday, July 16, 2018 12:54 PM
  • There were some logic/syntax issues in the previous queries...pls check the corrected versions below:

    select datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0) end, 1)) AS Date_To_new
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To_old
    
    select cast(year([Date_From]) as varchar(10)) + RIGHT('0' + RTRIM(cast(MONTH([Date_From]) as varchar(10))), 2) AS Period_from
    
    select case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end AS Months
    
    select cast(year(dateadd(day, 1, [Date_To])) as varchar(10)) + RIGHT('0' + RTRIM(cast(month(dateadd(day, 1, [Date_To])) as varchar(10))), 2) AS Renewal_Period

    Thank you, I am still getting an error

    Cannot construct data type date, some of the arguments have values which are not valid.

    Any idea how I resolve this please? I have tried to resolve by using this piece of code but that errors also

    datefromparts(datepart(year([Date_in]), datepart(month([Date_in]), 1) AS Date_From,

    Monday, July 16, 2018 1:17 PM
  • Which query you are getting error?
    Monday, July 16, 2018 1:24 PM
  • There were some logic/syntax issues in the previous queries...pls check the corrected versions below:

    select datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],datefromparts(year([Date_in]), month([Date_in]), 1)))/(365/12),0) end, 1)) AS Date_To_new
    
    select dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To_old
    
    select cast(year([Date_From]) as varchar(10)) + RIGHT('0' + RTRIM(cast(MONTH([Date_From]) as varchar(10))), 2) AS Period_from
    
    select case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end AS Months
    
    select cast(year(dateadd(day, 1, [Date_To])) as varchar(10)) + RIGHT('0' + RTRIM(cast(month(dateadd(day, 1, [Date_To])) as varchar(10))), 2) AS Renewal_Period

    Thank you, I am still getting an error

    Cannot construct data type date, some of the arguments have values which are not valid.

    Any idea how I resolve this please? I have tried to resolve by using this piece of code but that errors also

    datefromparts(datepart(year([Date_in]), datepart(month([Date_in]), 1) AS Date_From,

    this is again wrong

    why did you add datepart?

    I already gave you the suggestion for this

    datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From,

    is enough


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 1:24 PM
    Moderator
  • select datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From
    Monday, July 16, 2018 1:37 PM
  • select datefromparts(year([Date_in]), month([Date_in]), 1) AS Date_From
    This query didn't work?
    Monday, July 16, 2018 1:43 PM
  • I'm so sorry, I pasted in the wrong part of the code, the one above works fine thanks, it's this one that's erroring:

    dateadd(day, -1, datefromparts(year([Date_in]), month([Date_in]) + case when round((datediff(day, [Date_Required],[Date_in]))/(365/12),0)=0 then 1 else round((datediff(day, [Date_Required],[Date_in]))/(365/12),0) end, 1)) AS Date_To

    Error: 

    Cannot construct data type date, some of the arguments have values which are not valid.

    Monday, July 16, 2018 1:48 PM
  • I guess the error is due to an invalid value coming as the second argument to datefromparts function. So you have to examine your data and check whether there is possibility of an overflow for the month argument. 

    For Example, the statement 

    select datefromparts(2018, 14, 1)

    yields the same error you posted. It is because the month field is passed as 14 which invalid

    Similar issue should be happening in your case. So you have to examine your data and make necessary adjustments in the query..

    Monday, July 16, 2018 4:53 PM