July 16, 2018 at 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,
July 16, 2018 at 6:59 am
steven.littlewood16 - Monday, July 16, 2018 3:53 AMHi,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.
July 17, 2018 at 12:20 pm
steven.littlewood16 - Monday, July 16, 2018 3:53 AMHi,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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 17, 2018 at 1:02 pm
sgmunson - Tuesday, July 17, 2018 12:20 PMsteven.littlewood16 - Monday, July 16, 2018 3:53 AMHi,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).
July 17, 2018 at 2:35 pm
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
Don't fear failure, fear regret.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply