Tuesday, May 7, 2019

SQL Server - How to get difference between two dates

If you are working as a SQL Professional then there is a lot of activities which are related to dates. You have to write SQL queries to find out the difference between two dates or need to get the next day, next month, next week, next quarter or next year to set some business logic. 
Business Scenario 
Suppose that you need to send an email alert to your customers to remind them to pay the invoice bill after 30 days of invoice generation as per their contract. Hence, you have to write an SQL based program to check all the unpaid invoices which comes under this conditions. 
Here, In your SQL based program (it may be a stored procedure which may be schedule to run on the daily basis on the defined schedule), you have to get the difference between the invoice date and current running date. 
If the difference result is matched with the contract with the customer then system will pull that invoice and send it to the customer as the email attachment. 
To full-fill the above business needs, you have to use some defined SQL Date related functions such as -
DateDIFF(interval, date1, date2) is used to get the differences between two dates and return numeric values such as difference in years, months, quarters, days, weeks, hours, minutes, seconds, milliseconds and nanoseconds etc. The interval of time is used to calculate the difference between date1 and date2. It can be one of the following values:
Value (any one of)
Explanation
year, yyyy, yy
Year interval
quarter, qq, q
Quarter interval
month, mm, m
Month interval
dayofyear
Day of year interval
day, dy, y
Day interval
week, ww, wk
Week interval
weekday, dw, w
Weekday interval
hour, hh
Hour interval
minute, mi, n
Minute interval
second, ss, s
Second interval
millisecond, ms
Millisecond interval

Examples
---- Difference in years
SELECT DATEDIFF(year, '2012/04/28', '2014/04/28');
Result: 2
---- Difference in months
SELECT DATEDIFF(month, '2014/01/01', '2014/04/28');
Result: 3
---- Difference in days
SELECT DATEDIFF(day, '2014/01/01', '2014/04/28');
Result: 117
---- Difference in hours
SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 2
---- Difference in minutes
SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 165

To watch a demo video, please visit our YouTube channel -

Monday, May 6, 2019

SQL Server - Treat Null Values in Count() function

As we know that the COUNT function can tell us the total number of rows returned in a result set (both NULL and non-NULL together but it is also depending on how it’s used). 


For example, 

  • Using SELECT COUNT(*) or SELECT COUNT(1)  (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values. 
This is because, we are not specifying any expression in the count function. So, this function is returning all the rows including null values also.
  • Using COUNT (<Specific Column Name Here>will count the number of non-NULL items in the specified column (NULL fields will be ignored).
  • COUNT (<expression>) counts rows where the <expression> is not null.
Thus, we could find the number of NULL fields in the result set by subtracting the non-NULL fields from the Total fields. 

Note: In SQL Server, since the COUNT (and other aggregate functions) will ignore NULL values, we use the CASE to turn NULLs into values and values into NULLs


In the below example, we are creating a data-set with the help of common table expression and using the count function -
--This will return count of 2
;WITH CTE
    AS (
    SELECT NULL AS Id
    UNION ALL
    SELECT 1 AS Id
    )
    SELECT COUNT(*) FROM CTE

--This will return count of 1
;WITH CTE
    AS (
    SELECT NULL AS Id
    UNION ALL
    SELECT 1 AS Id
    )
    SELECT COUNT (Id)  FROM  CTE

 So, it depends on you, how can you use count function in your SQL scripts. 



Popular Posts

Get Sponsored by Big Brands