I had a requirement to extract the data modified during yesterday’s date and for Mondays, the client wanted to be able to extract the data modified on the last Friday and during the weekend.
It should be noted that the table contained a field called “ModifiedDate” and the following was added to the SQL query to calculate the start and end dates and check whether today “the extraction date” is Monday, if so then return the data for the last three days rather than just the previous day and if not Monday then returns only the data modified during the previous date.
DECLARE @startDateCount AS DATETIME
DECLARE @endDateCount AS DATETIME
DECLARE @today AS DATETIME
SET @today = CONVERT(VARCHAR(10), GETDATE(), 101)
SET @endDateCount = @today
IF (DATENAME(dw, GETDATE()) = 'Monday')
SET @startDateCount = @today - 3
ELSE
SET @startDateCount = @today - 1
-- TO TEST
-- SELECT @startDateCount AS [START DATE], @endDateCount AS [END DATE], @today AS TODAY
--SELECT * FROM TABLENAME--WHERE FIELDNAME BETWEEN @startDateCount AND @endDateCount OR
I could have used the @today alone but to make it easier to read, I preferred to create an extra @endDateCount date. Also, the above contained a few useful Transact-SQL functions such as the CONVERT which converts the DATE value into VARCHAR in a format of your choice. The format chosen above is MM/DD/YYYY, this is specified by the third parameter of the function which is 101 as shown above. For a complete list of these formats, check out the following blog: http://www.sql-server-helper.com/tips/date-formats.aspx
Also, the DATENAME function which returns the character string representing the specified datepart of the specified date. For further info on this function, check the MSDN link below: http://msdn2.microsoft.com/en-us/library/ms174395.aspx
The link to my original post: Get data modified yesterday and/or during the weekend
