SQL Date Processing and conversion, SQL date processing conversion

SQL Date Processing and conversion, SQL date processing conversion

DECLARE @ dt datetime SET @ dt = GETDATE () DECLARE @ number int SET @ number = 3 -- 1. specify the first or last day of the year --. select convert (char (5), @ dt, 120) + '1-1' -- B. select convert (char (5), @ dt, 120) + '12-31 '-- 2. the first or last day of the quarter where the specified date is located --. select convert (datetime, CONVERT (char (8), DATEADD (Month, DATEPART (Quarter, @ dt) * 3-Month (@ dt)-2, @ dt), 120) + '1') -- B. select convert (datetime, CONVERT (char (8), DATEADD (Month, DATEPART (Quarter, @ dt) * 3-Month (@ dt ), @ dt), 120) + case when datepart (Quarter, @ dt) in () THEN '31 'else' 30' END) -- C. select dateadd (Day,-1, CONVERT (char (8), DATEADD (Month, 1 + DATEPART (Quarter, @ dt) * 3-Month (@ dt), @ dt), 120) + '1') -- 3. specifies the first or last day of the month in which the date is located --. select convert (datetime, CONVERT (char (8), @ dt, 120) + '1') on the first day of the month -- B. select dateadd (Day,-1, CONVERT (char (8), DATEADD (Month, 1, @ dt), 120) + '1') -- C. last Day of the Month (easy to use error method) select dateadd (Month, 1, DATEADD (DAY,-Day (@ dt), @ dt) -- 4. select dateadd (Day, @ number-DATEPART (Weekday, @ dt), @ dt) -- 5. specify the number of any day of the week in which the date is located --. sunday as the Day of the week select dateadd (Day, @ number-(DATEPART (Weekday, @ dt) + @ DATEFIRST-1) % 7, @ dt) -- B. monday as the Day of the week select dateadd (Day, @ number-(DATEPART (Weekday, @ dt) + @ DATEFIRST-2) % 7-1, @ dt)