Convert int field into date and format issue
-
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! -
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 -
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!
- daniness - Monday, October 15, 2018 1:44 PM
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 -
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!
- daniness - Monday, October 15, 2018 1:54 PM
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 -
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.
-
Try this:
Declare @acctPeriod int = 201801;
Select dateadd(month, -1, datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1))Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams 3188 - Monday, October 15, 2018 2:37 PM
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
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 PeriodEndCalendarI'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!
- daniness - Monday, October 15, 2018 2:49 PM
Instead of subtracting 1 month, subtract 1 day
SELECT DATEADD(DAY, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar -
Here is a link to some Common Date Routines
- DesNorton - Monday, October 15, 2018 2:51 PM
Thanks @desnorton. That did it! You're truly brilliant!
- daniness - Monday, October 15, 2018 2:49 PM
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 - 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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply