Category: Date & Time

Find Max and Min value of dates in a row in SQL Server

Problem Statement:
How to identify the Max and Min date value in a row in SQL Server?

Code:
Here is a solution to identify Max and Min date value with the help of CROSS APPLY in SQL Server.


create Table FindMaxMindate
(
    ID int, 
    Firstdate datetime,
    Seconddate datetime, 
    ThirdDate datetime
)

Insert into FindMaxMindate 
Values
(1,'2018-01-26 19:14:07.587','2018-04-26 19:14:07.587','2018-12-26 19:14:07.587'),
(2,'2018-12-26 19:14:07.587','2018-01-26 19:14:07.587','2018-03-26 19:14:07.587')

Select ID,Min(Dates) MinDate,Max(Dates) MaxDate
From FindMaxMindate A
Cross apply (values (Firstdate),(SecondDate),(ThirdDate))B (Dates)
Group by ID

Drop Table FindMaxMinDate
Advertisements

CURRENT_TIMESTAMP in SQL Server

CURRENT_TIMESTAMP is a system function to retrieve the current system date and time. CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function. That says it is a non-deterministic function. If you are very specific to ANSI comaptibility or thinking of heterogenous system support, I would suggest to use CURRRENT_TIMESTAMP instead of GETDATE(). As an envision, I prefer to use CURRENT_TIMESTAMP over GETDATE() personally wherever possible.

Few of Thoughts:

1. When we create a default constaint with CURRENT_TIMESTAMP, by default, SSMS properties are showing GETDATE().


My thought: This could be because SSMS properties have nothing to do with ANSI compatibility nor hetrogeneous support.

2. Tool tip text of the function adds parenthesis – ()

My thought: I have no clue why Microsoft has made this so.Its more concerned to me the function with “()” gives an error as below.

First and Last funda with dates in SQL Server

This post is to provide scripts to identify FIRST and LAST usages for a given date.


--How to get first day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

--How to get Last day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0));

--How to get first day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE), 0) AS STARTOFYEAR

--How to get Last day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE) + 1, -1) AS ENDOFYEAR

I will add more usages of FIRST/LAST day to the above list as I come across in future.