October 15, 2018 at 1:20 pm
Hi All,
I'm hoping to get your advice on this. I'm trying to take an int field, AccountingPeriod, value, convert it to a date and then format it to populate a date field, but it's not getting the correct value for the month. Here's the query I'm using:
,LEFT(ppr.ReportDateYearMonth,4) + RIGHT(DateAdd(mm,-1,(CONVERT(date,(CAST(ppr.ReportDateYearMonth AS datetime))))),6) AS PeriodEndCalendar
,ppr.ReportDateYearMonth AS AccountingPeriod
but it's not showing with the correct month value, which should be 1 month earlier than the one that is in the AccountingPeriod:
Can someone please advise on why this isn't working? Thanks!
October 15, 2018 at 1:30 pm
It appears that your ReportDateYearMonthg is a char(6) value.
This should do the trickSELECT DATEADD(MONTH, -1, CONVERT(date, ppr.ReportDateYearMonth+'01', 112)) AS PeriodEndCalendar
October 15, 2018 at 1:44 pm
Hi @desnorton,
Thanks for your suggestion, but when I try it, I'm getting an "Explicit conversion from data type int to date is not allowed" error...the ReportDateYearMonth field is actually type int. Any other suggestions? Thanks!
October 15, 2018 at 1:47 pm
daniness - Monday, October 15, 2018 1:44 PMHi @desnorton,Thanks for your suggestion, but when I try it, I'm getting an "Explicit conversion from data type int to date is not allowed" error...the ReportDateYearMonth field is actually type int. Any other suggestions? Thanks!
OK, so you need to cast the int to a char ...SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
October 15, 2018 at 1:54 pm
Thanks, @desnorton....that actually fixed the issue! You're a genius! Could you please explain why that worked? I'm not clear on why we needed to cast to a char. Thanks again!
October 15, 2018 at 2:07 pm
daniness - Monday, October 15, 2018 1:54 PMThanks, @desnorton....that actually fixed the issue! You're a genius! Could you please explain why that worked? I'm not clear on why we needed to cast to a char. Thanks again!
Your current YearMonth value is stored as an integer. However, the integer number has no computed meaning in respect of the actual date.
In order to cast it to a date, it needs to be broken down into YEAR + MONTH + DAY. So by casting the 6 digit value a char, you get "YYYYMM". We then add the '01' as a char(2) to get "YYYYMMDD", which is the ISO standard format for dates. The CONVERT(date, "YYYYMMDD", 112) then casts the value to a valid date.
Alternatively, we could multiply the YearMonth value by 100 and add 1 to get an integer that looks like "YYYYMMDD", and convert that to a char(8). We can then use the CONVERT() to cast it to a valid date.SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(@ReportDateYearMonth *100 +1 AS char(8)), 112)) AS PeriodEndCalendar
October 15, 2018 at 2:10 pm
Sorry, but that doesn't read right. I seem to be having trouble articulating what my mind is thinking.
Perhaps the CONVERT() documentation might help.
October 15, 2018 at 2:37 pm
Try this:
Declare @acctPeriod int = 201801;
Select dateadd(month, -1, datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1))
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2018 at 2:40 pm
Jeffrey Williams 3188 - Monday, October 15, 2018 2:37 PMTry this:
Declare @acctPeriod int = 201801;
Select dateadd(month, -1, datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1))
And if you really want the last day of the end of the period:
Declare @acctPeriod int = 201801;
Select eomonth(datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1), -1)
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2018 at 2:49 pm
Thanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.
SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
I've tried the EOMONTH function, but it's not looking correct, as I checked and it's returning 30 for some of the months where there are 31 days:
DATEADD(MONTH, -1, EOMONTH(CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112))) AS PeriodEndCalendar
I'd appreciate any pointers on this. Thanks!
October 15, 2018 at 2:51 pm
daniness - Monday, October 15, 2018 2:49 PMThanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.
SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
I'd appreciate any pointers on this. Thanks!
Instead of subtracting 1 month, subtract 1 daySELECT DATEADD(DAY, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
October 15, 2018 at 2:59 pm
Here is a link to some Common Date Routines
October 15, 2018 at 3:02 pm
DesNorton - Monday, October 15, 2018 2:51 PMdaniness - Monday, October 15, 2018 2:49 PMThanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.
SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
I'd appreciate any pointers on this. Thanks!
Instead of subtracting 1 month, subtract 1 day
SELECT DATEADD(DAY, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
Thanks @desnorton. That did it! You're truly brilliant!
October 15, 2018 at 3:05 pm
daniness - Monday, October 15, 2018 2:49 PMThanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.
SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar
I've tried the EOMONTH function, but it's not looking correct, as I checked and it's returning 30 for some of the months where there are 31 days:
DATEADD(MONTH, -1, EOMONTH(CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112))) AS PeriodEndCalendar
I'd appreciate any pointers on this. Thanks!
The EOMONTH function IS working correctly. The problem is that you are processing your calculations in the wrong order. You need to subtract one month BEFORE finding the EOMONTH rather than AFTER.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2018 at 7:37 pm
daniness - Monday, October 15, 2018 1:20 PMHi All,I'm hoping to get your advice on this. I'm trying to take an int field, AccountingPeriod, value, convert it to a date and then format it to populate a date field, but it's not getting the correct value for the month. Here's the query I'm using:
,LEFT(ppr.ReportDateYearMonth,4) + RIGHT(DateAdd(mm,-1,(CONVERT(date,(CAST(ppr.ReportDateYearMonth AS datetime))))),6) AS PeriodEndCalendar
,ppr.ReportDateYearMonth AS AccountingPeriod
but it's not showing with the correct month value, which should be 1 month earlier than the one that is in the AccountingPeriod:
Can someone please advise on why this isn't working? Thanks!
For the life of me, I'll never understand why people use "PeriodEnd" instead of the much simpler in all ways "PeriodStart".
--Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply