Convert Access SQL to SQL Server

  • steven.littlewood16

    Grasshopper

    Points: 18

    Hi,

    I am new to SQL server and Access and I really need to convert an Access SQL query into a SQL Server query but really don't know how to. Apologies that there are a few lines of code that I need help with but if anyone is able to convert the following for me that would be really helpful . Being so new into this make it hard to know what functions and syntax is required until I attend my training course.  

    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,

  • Luis Cazares

    SSC Guru

    Points: 183499

    steven.littlewood16 - Monday, July 16, 2018 3:53 AM

    Hi,

    I am new to SQL server and Access and I really need to convert an Access SQL query into a SQL Server query but really don't know how to. Apologies that there are a few lines of code that I need help with but if anyone is able to convert the following for me that would be really helpful . Being so new into this make it hard to know what functions and syntax is required until I attend my training course.  

    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,

    I'm not used to Access SQL, so I could appreciate some sample data, expected results based on that sample data and a short explanation on what each row does.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • sgmunson

    SSC Guru

    Points: 110418

    steven.littlewood16 - Monday, July 16, 2018 3:53 AM

    Hi,

    I am new to SQL server and Access and I really need to convert an Access SQL query into a SQL Server query but really don't know how to. Apologies that there are a few lines of code that I need help with but if anyone is able to convert the following for me that would be really helpful . Being so new into this make it hard to know what functions and syntax is required until I attend my training course.  

    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,

    Need to know the data type of the [Date_in], [Date_Required], and [Date_To] columns.   However, it appears that line 1 might be replaceable using the DATEFROMPARTS function in addition to the YEAR and MONTH functions, which work the same in SQL Server as they do in MS Access.   Wherever you see 1 being added to a date, you can substitute DATEADD(day, 1, [Date_Column]), where you substitute in the date column that was previously having 1 added to it.   There is also some math that assumes an exact 30 days for a month's duration in lines, 2, 3, 4, and 6.   Lines 5 and 7 form character-based values to represent a period, composed of a year and month, guaranteeing a two-digit month value.   Don't have time for more detail at the moment, but this should get you started.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Luis Cazares

    SSC Guru

    Points: 183499

    sgmunson - Tuesday, July 17, 2018 12:20 PM

    steven.littlewood16 - Monday, July 16, 2018 3:53 AM

    Hi,

    I am new to SQL server and Access and I really need to convert an Access SQL query into a SQL Server query but really don't know how to. Apologies that there are a few lines of code that I need help with but if anyone is able to convert the following for me that would be really helpful . Being so new into this make it hard to know what functions and syntax is required until I attend my training course.  

    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,

    Need to know the data type of the [Date_in], [Date_Required], and [Date_To] columns.   However, it appears that line 1 might be replaceable using the DATEFROMPARTS function in addition to the YEAR and MONTH functions, which work the same in SQL Server as they do in MS Access.   Wherever you see 1 being added to a date, you can substitute DATEADD(day, 1, [Date_Column]), where you substitute in the date column that was previously having 1 added to it.   There is also some math that assumes an exact 30 days for a month's duration in lines, 2, 3, 4, and 6.   Lines 5 and 7 form character-based values to represent a period, composed of a year and month, guaranteeing a two-digit month value.   Don't have time for more detail at the moment, but this should get you started.

    Considering that the OP is using a version where it's available (should be by now, but not always happening).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • below86

    SSChampion

    Points: 11186

    Here is my thoughts on what this converts to, sort of.  See comments for questions I had. This is not a one-to-one conversion, it is my thoughts on the equivalent way to do the same thing in SQL.  It's been a long time since I looked at Access SQL.

    DECLARE @Date_in AS DATE;
    DECLARE @Date_Required AS DATE;

    SET @Date_in = GETDATE();

    SELECT @Date_in AS Date_in--, @Date_Required AS Date_Required;
    -- Returns 2018-07-17

    --1)DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in])),1) AS Date_From,
    -- sets the date to the first of the month
    SET @Date_Required = (SELECT DATEADD(DD, -(DATEPART(DD, @Date_in)-1), @Date_in));

    -- If Date_Required that is set in the previous step is what is used in #2, 3, and 4, then I think all 3 would come to the same answer

    --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,
    -- This looks to be seeting the 'Date_To' to the last day of the month
    SELECT DATEADD(DD, -1, DATEADD(MM, 1, @Date_Required)) 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,
    --SELECT LEFT(CONVERT(CHAR(10), Date_From, 112), 6) 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,
    -- If this is the Date_Required from above, wouldn't this always be 1?

    --7) Year([Date_To]+1) & Format(Month([Date_To]+1),"00") AS Renewal_Period,
    SELECT LEFT(CONVERT(CHAR(10), DATEADD(YY, 1, DATEADD(MM, 1, Date_To)), 112), 6) AS Renewal_Period

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply