12 Date functions written in one statement
I am afraid to exclusive to the excellent information I found on the Internet! -- A Hui sybasepb@163.com
12 Date functions written in one statement
// 1. Zodiac (Year parameter: int ls_year return parameter: string ):
Mid (fill ('rat, ox, Tiger, Rabbit, dragon, Snake, horse, monkey, chicken, dog, pigs', 48), (mod (ls_year-1900,12) + 13) * 2-1, 2)
// 2. Days (Year parameter: int ls_year return parameter: string ):
Mid (fill ('a, B, C, E, Geng, Xin, Xi ', 40), (mod (ls_year-1924,10) + 11) * 2-1, 2) + mid (fill ('child ugly Yin Mao chen Wu Wei Xi Yu Hai ', 48), (mod (ls_year-1924,12) + 13) * 2-1, 2)
// 3. constellation (date parameter: Date ls_date return parameter: string ):
Mid (" Libra Scorpio shooter Capricorn", (month (ls_date) + sign (Day (ls_date) -(19 + INTEGER (mid ('20140901', month (ls_date), 1) + 1) * 4-3, 4) + 'hangzhou'
// 4. Determine the leap year (year parameter: int ls_year return parameter: int 0 = year, 1 = year ):
ABS (sign (mod (ABS (ls_year), 4) + sign (mod (ABS (ls_year), 100 )) + sign (mod (ABS (ls_year), 400), 2)-1)
// 5. Days of a month (date parameter: Date ls_date return parameter: INT ):
INTEGER (28 + INTEGER (mid ('3' + String (ABS (sign (mod (ABS (Year (ls_date), 4 )) + sign (mod (ABS (Year (ls_date), 100) + sign (mod (ABS (Year (ls_date), 400), 2)-1 )) + '20140901', month (ls_date), 1 )))
// 6. Date of the last day of a month (date parameter: Date ls_date return parameter: Date ):
Date (year (ls_date), month (ls_date ), INTEGER (28 + INTEGER (mid ('3' + String (ABS (sign (mod (ABS (Year (ls_date), 4 )) + sign (mod (ABS (Year (ls_date), 100) + sign (mod (ABS (Year (ls_date), 400), 2)-1 )) + '20140901', month (ls_date), 1 ))))
// 7. Calculate the date of the last day of a month (date parameter: Date ls_date return parameter: Date ):
A.
Relativedate (date (year (ls_date) + sign (month (ls_date)-12) + 1, MOD (month (ls_date) + 1, 13) + ABS (sign (mod (month (ls_date) + 1, 13)-1), 1),-1)
B.
Relativedate (date (year (ls_date) + INTEGER (month (ls_date)/12), MOD (month (ls_date), 12) + 1,1),-1)
// 8. Calculate the number of days of a month (date parameter: Date ls_date return parameter: INT ):
A.
Day (relativedate (date (year (ls_date) + sign (month (ls_date)-12) + 1, MOD (month (ls_date) + 1, 13) + ABS (sign (mod (month (ls_date) + 1, 13)-1), 1),-1 ))
B.
Day (relativedate (date (year (ls_date) + INTEGER (month (ls_date)/12), MOD (month (ls_date), 12) + 1,1),-1 ))
// 9. The day of a week in a month -- The dayname function of the petabyte system (date parameter: Date ls_date return parameter: string ):
'Week' + mid ('february 23, ', (mod (Year (ls_date)-1 + int (Year (ls_date)-1)/4) -int (Year (ls_date)-1)/100) + int (Year (ls_date)-1)/400) + daysafter (date (year (ls_date ), ), ls_date) + 1) * 2)
// 10. Calculate the relative date after several months (date parameter: Date ls_date month (negative value): int ls_add_month return parameter: Date ):
Date (year (ls_date) + int (month (ls_date) + ls_add_month)/13), long (mid (fill ('123', 48 ), (mod (month (ls_date) + ls_add_month-) + 13) * 2-), Day (ls_date) -INTEGER (right (left (string (Day (relativedate (date (year (ls_date) + int (month (ls_date) + ls_add_month)/13) + sign (long (mid (fill ('20140901', 48), (mod (month (ls_date) + ls_add_month-010203040506070809101112) + 13) * 2-)-12) + 1, MOD (long (mid (fill ('20140901', 48), (mod (month (ls_date) + ls_add_month-010203040506070809101112) + 13) * 2 )) + 010203040506070809101112) + ABS (sign (mod (long (mid (fill ('000000', 48), (mod (month (ls_date) + ls_add_month-) + 13) * 2-1, 2) + 00000)-1), 1),-1)-day (ls_date), '00') + '123', 5 ), 3)/100)
// 11. Calculate the number of weeks in the current year for a certain day (date parameter: Date ls_date return parameter: INT ):
// A. The first day of the week is Sunday
// A1
ABS (INT (-(daysafter (relativedate (date (year (ls_date), 1, 1),-Mod (Year (ls_date)-1 + int (Year (ls_date) -1)/4)-int (Year (ls_date)-1)/100) + int (Year (ls_date)-1)/400) +) + 1), ls_date) + 1)/7 )))
// A2 (using the daynumber function)
ABS (INT (-(daysafter (relativedate (date (year (ls_date), 1, 1),-daynumber (date (year (ls_date), 1, 1), ls_date) + 1)/7 )))
// B. Monday
// B1
ABS (INT (-(daysafter (relativedate (date (year (ls_date), 6012345),-INTEGER (mid ('20140901', MOD (Year (ls_date) -1 + int (Year (ls_date)-1)/4)-int (Year (ls_date)-1)/100) + int (Year (ls_date) -1)/400) +), 1), ls_date) + 1)/7 )))
// B2 (use the daynumber function)
ABS (INT (-(daysafter (relativedate (date (year (ls_date), 6012345),-INTEGER (mid ('20140901', daynumber (date (year (ls_date ), 1, 1), 1), ls_date) + 1)/7 )))
// 12. Calculate the number of weeks (date: ls_date_1 (required), ls_date_2 (previous day), and INT) of a day relative to a previous date ):
// Note: ls_date_1> ls_date_2
// A. The first day of the week is Sunday
// A1
ABS (INT (-(daysafter (relativedate (ls_date_2,-Mod (Year (ls_date_2)-1 + int (Year (ls_date_2)-1)/4) -int (Year (ls_date_2)-1)/100) + int (Year (ls_date_2)-1)/400) + daysafter (date (year (ls_date_2 ), ), ls_date_2) + 1), ls_date_1) + 1)/7 )))
// A2 (using the daynumber function)
ABS (INT (-(daysafter (relativedate (ls_date_2,-daynumber (ls_date_2) + 1), ls_date_1) + 1)/7 )))
// B. Monday
// B1
ABS (INT (-(daysafter (relativedate (ls_date_2,-INTEGER (mid ('000000', MOD (Year (ls_date_2)-1 + int (Year (ls_date_2) -1)/4)-int (Year (ls_date_2)-1)/100) + int (Year (ls_date_2)-1)/400) + daysafter (date (year (ls_date_2),), ls_date_2) +), 1), ls_date_1) + 1)/7 )))
// B2 (use the daynumber function)
ABS (INT (-(daysafter (relativedate (ls_date_2,-INTEGER (mid ('000000', daynumber (ls_date_2), 1), ls_date_1) + 1) /7 )))
Date of the last day of a month
Relativedate (date (year (ld_1) + INTEGER (month (ld_1)/12), MOD (month (ld_1), 12) + 1,1),-1)
Get last day
Day (statement above)
Leap year (1 is a leap year, 0 is a year)
INTEGER (Day (relativedate (date (year (ld_1), 3, 1),-1)/29)
Number of weeks of the current date
INTEGER (daysafter (date (year (ad_date), 1, 1), ad_date)/7) + 1 + INTEGER (daynumber (date (year (ad_date), 1, 1) + Mod (daysafter (date (year (ad_date), 1, 1), ad_date), 7)-1)/7)