T-SQL: Date-Related Queries
Finding Day Number from the Beginning of the Year
I want to start with this simple question that was posted today (May 31, 2013) - how to find today's date day number from the beginning of the year.
This is my solution and a bit of explanation at the end
DECLARE @curDate DATE = CURRENT_TIMESTAMP;
DECLARE @YearStart DATE = dateadd (
year
,datediff(year, '19000101', @curDate)
,'19000101'
);
SELECT datediff(day, @YearStart, @curDate) + 1 AS [Number of Days from the Year Start]
The @YearStart variable dynamically calculates the beginning of the year for any date based on the year difference with any known date we use as anchor date.
However, there is much simpler solution as suggested by Gert-Jan Strick in the thread I referenced:
SELECT datepart(dayofyear, current_timestamp) AS [Number of Days]
Finding Beginning and Ending of the Previous Month
Today's Transact-SQL MSDN forum presented the following problem Change date parameters to find data from previous month.
I will give my solution to this problem from that thread:
DECLARE @MinDate DATETIME, @MaxDate DATETIME;
SET @MinDate = DATEADD(month, DATEDIFF(month, '19000201', CURRENT_TIMESTAMP), '19000101');
SET @MaxDate = DATEADD(day,-1,DATEADD(month, 1, @MinDate)) -- for versions prior to SQL 2012;
SET @MaxDate = EOMONTH(@MinDate); -- for SQL Server 2012 and up
Combining Date and DateTime (time only) columns into one DateTime column
DECLARE @date date='20130318', @time datetime ='4:15:05 PM';
SELECT dateadd(day, datediff(day, '19000101', @date), @time);
See Also
This entry participated in the TechNet Guru Contribution for May 2013competition and won the Bronze medal.