Obtain last day of month

[har]

hey guys,
The below code is written in sql which obtains the last day of month based upon new month and year values.
SET @li_last_day_month = DATEPART(dd,(DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,'01/01/1900', RTRIM(CAST(@li_calcmonth AS CHAR(2))) + '/01/' + RTRIM(CAST(@li_calcyear AS CHAR(4)))) + 1,'01/01/1900'))))
i m not able to figure out this compleate logic indatastage..
thanks for further help....

thanks
har

[chulett]

Search is your friend. Check here for one discussion on the topic.

[sumitgulati]

Say you get an input date and you have to get the number of days in the month. Say the input date is m/d/y Follow the following steps:
1) Add one month to the date (m+1).
2) Hard code the date to 1. So your new date becomes (m+1)/1/y
3) Now substract one day from this new date. You will get last date in your original month.
4) To get the number of days in your original month substract the first date of the original month from the last date.

Here is the code:

Code: Select all

y1 = date1[1,4]
m1 = date1[6,2]

InYearDate1 = y1
InMonthDate1 = m1 + 1
If InMonthDate1 > 12 Then
               InYearDate1 = y1 + 1
               InMonthDate1 = 1
End

EndDtOfMonth1 = Iconv("1/":InMonthDate1:"/":InYearDate1, "D/DMY") - 1
LastDayOfMon1 = OConv(EndDtOfMonth1, "D")

DiffInDaysForFirstDate = (Field(LastDayOfMon1, " ", 1)) - d1 ; 
NoOfDaysInFirstDtsMonth = (Field(LastDayOfMon1, " ", 1)) - Field(OConv(Iconv("1/":m1:"/":y1, "D/DMY"), "D"), " ", 1) + 1

It is an excerpt from a big code I had so please excuse me if there is any copy-paste error.

Regards,
Sumit

[sumitgulati]

Sorry there is a little error in the previous code I sent. Here is the code to get number of days in a month:

The input date should be in YYYY-MM-DD format. You can write a routine (Transform Function) with one input argument called date1.

Code: Select all

y1 = date1[1,4]
m1 = date1[6,2]
d1 = date1[9,2]

InYearDate1 = y1
InMonthDate1 = m1 + 1
If InMonthDate1 > 12 Then
               InYearDate1 = y1 + 1
               InMonthDate1 = 1
End

EndDtOfMonth1 = Iconv("1/":InMonthDate1:"/":InYearDate1, "D/DMY") - 1
LastDayOfMon1 = OConv(EndDtOfMonth1, "D")

DiffInDaysForFirstDate = (Field(LastDayOfMon1, " ", 1)) - d1 ; 
NoOfDaysInFirstDtsMonth = (Field(LastDayOfMon1, " ", 1)) - Field(OConv(Iconv("1/":m1:"/":y1, "D/DMY"), "D"), " ", 1) + 1

Ans = NoOfDaysInFirstDtsMonth

Hope it helps.

Regards,
-Sumit

[chulett]

Or... if you looked at the thread I linked to, you could simply use the MONTH.LAST transform. No code required.

[sumitgulati]

Oh yes the code actually is not required. We can also use ConvertMonth with option "L". I should have checked the link sent by you first before posting the code.
Well, since I have already posted the code it might give him an idea of the logic used in ConvertMonth routine.

Thanks Chulett
Sumit

[rreyespe]

If you first obtain the month, then you can use the following expresion to get the last day of the month:

OConv(MONTH.LAST(MES),'D-YMD[4,2,2]')

being the format of variable MES the following: YYYY-MM

[har]

Thanx for ur great help

har