--1.come up 2 more ways to go back to the beginning of any date (hint: format, concat,…
- User
--1.come up 2 more ways to go back to the beginning of any date (hint: format, concat, datepart)
select getdate(), dateadd(month, datediff(month,0,getdate()),0), dateadd(month, datediff(month,0,'2015-04-28'),0), format(getdate(),'yyyy-MM-01'), format(cast('2015-04-28' as date),'yyyy-MM-01')
- Assistant
-
Using the CONCAT function: select getdate(), dateadd(month, datediff(month,0,getdate()),0), dateadd(month, datediff(month,0,'2015-04-28'),0), format(getdate(),'yyyy-MM-01'), format(cast('2015-04-28' as date),'yyyy-MM-01'), CAST(DATEPART(year, getdate()) AS VARCHAR(4)) + '-' + CAST(DATEPART(month, getdate()) AS VARCHAR(2)) + '-01'
-
Using the DATEPART function: select getdate(), dateadd(month, datediff(month,0,getdate()),0), dateadd(month, datediff(month,0,'2015-04-28'),0), format(getdate(),'yyyy-MM-01'), format(cast('2015-04-28' as date),'yyyy-MM-01'), DATEPART(year, getdate()) + '-' + DATEPART(month, getdate()) + '-01'
-
- User
come up 2 more ways to calculate first day of any month
- Assistant
-
Using the DATEADD and DATEDIFF functions: SELECT DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) AS FirstDayOfMonth;
-
Using the YEAR, MONTH, and DAY functions: SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-01' AS FirstDayOfMonth;
-
- User
come up 2 more ways to calculate first day of any month june
- Assistant
-
Using the DATEFROMPARTS function: SELECT DATEFROMPARTS(YEAR(GETDATE()), 6, 1) AS FirstDayOfJune;
-
Using the CONCAT function: SELECT CONCAT(YEAR(GETDATE()), '-06-01') AS FirstDayOfJune;
-
- User
come up with ways to get first day of previous month(same as above)
- Assistant
-
Using the DATEADD and DATEDIFF functions: SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) AS FirstDayOfPreviousMonth;
-
Using EOMONTH and DATEADD functions: SELECT DATEADD(day, 1, EOMONTH(GETDATE(), -2)) AS FirstDayOfPreviousMonth;
-
- User
--come up with as many ways to get beginning of previous quarter and last day of previous quarter --come up with as many ways to get beginning and last day of current and previous weeks
- Assistant
To get the beginning of the previous quarter and the last day of the previous quarter, you can use the following methods:
-
Using the DATEADD, DATEDIFF, and DATEPART functions:
-
Beginning of Previous Quarter:
SELECT DATEADD(quarter, DATEDIFF(quarter,0,GETDATE()) - 1, 0) AS BeginningOfPreviousQuarter; -
Last Day of Previous Quarter:
SELECT DATEADD(day, -1, DATEADD(quarter, DATEDIFF(quarter,0,GETDATE()), 0)) AS LastDayOfPreviousQuarter;
-
-
Using the EOMONTH function:
-
Beginning of Previous Quarter:
SELECT DATEADD(quarter, DATEDIFF(quarter,0,GETDATE()) - 1, 0) AS BeginningOfPreviousQuarter; -
Last Day of Previous Quarter:
SELECT EOMONTH(DATEADD(quarter, DATEDIFF(quarter,0,GETDATE()), -1)) AS LastDayOfPreviousQuarter;
-
To get the beginning and last day of the current and previous weeks, you can use the following methods:
-
Using the DATEADD and DATEDIFF functions:
-
Beginning of Current Week:
SELECT DATEADD(week, DATEDIFF(week,0,GETDATE()), 0) AS BeginningOfCurrentWeek; -
Last Day of Current Week:
SELECT DATEADD(day, -1, DATEADD(week, DATEDIFF(week,0,GETDATE()) + 1, 0)) AS LastDayOfCurrentWeek; -
Beginning of Previous Week:
SELECT DATEADD(week, DATEDIFF(week,0,GETDATE()) - 1, 0) AS BeginningOfPreviousWeek; -
Last Day of Previous Week:
SELECT DATEADD(day, -1, DATEADD(week, DATEDIFF(week,0,GETDATE()), 0)) AS LastDayOfPreviousWeek;
-
-
Using the DATEPART and CONCAT functions:
-
Beginning of Current Week:
SELECT DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()) AS BeginningOfCurrentWeek; -
Last Day of Current Week:
SELECT DATEADD(day, 7 - DATEPART(weekday, GETDATE()), GETDATE()) AS LastDayOfCurrentWeek; -
Beginning of Previous Week:
SELECT DATEADD(day, -6 - DATEPART(weekday, GETDATE()), GETDATE()) AS BeginningOfPreviousWeek; -
Last Day of Previous Week:
SELECT DATEADD(day, -DATEPART(weekday, GETDATE()), GETDATE()) AS LastDayOfPreviousWeek;
-
-
- User
--5. split names into three seperate columns, firstname, lastname, middlename select 'Anna, Anna_lastname, Anna_middlename'
- Assistant
To split the name into separate columns for firstname, lastname, and middlename, you can use the STRING_SPLIT function and some string manipulation:
DECLARE @name VARCHAR(100) = 'Anna, Anna_lastname, Anna_middlename'; SELECT TRIM(value) AS firstname, LTRIM(RTRIM(SUBSTRING(value, CHARINDEX('_', value) + 1, CHARINDEX('_middlename', value) - CHARINDEX('_', value) - 1))) AS lastname, LTRIM(RTRIM(SUBSTRING(value, CHARINDEX('_middlename', value) + LEN('_middlename') + 1, LEN(value))))) AS middlename FROM STRING_SPLIT(@name, ',');This will split the name into three separate columns: firstname, lastname, and middlename. Make sure to replace
'Anna, Anna_lastname, Anna_middlename'with your actual column value or variable in the above code.