Category Archives: Scripts

How to get Quarter’s Start and End Date for a given date in Sql Server

Scripts, Sql Server, Tips/Tricks, , ,

We can use a query like below to get Quarter’s Start and End Date for a given date.

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

Quarter_Start_End_Date_In_SqlServer

Note the Quarter End Date returned by the above query is without time part. If we need to generate a report for a quarter then we need to include all the transactions happening on the last date of the quarter till mid night. Then in the query instead of comparing with last date of the quarter we can check whether it is less than the next quarter start date.

Below query shows how to get Quarter’s Start Date and the Next Quarter Start Date for a given date.

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
	DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                               AS 'Quarter Start Date',       
	DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0) 
                               AS 'Next Quarter Start Date'

Quarter_Start_And_Next_Quarter_Start_Date

How to find all tables that have specified column name in Sql Server?

Scripts, Sql Server, Tips/Tricks,

We can use a script like below to find all the tables in the database that have column with specified name in it:

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
     INNER JOIN sys.columns c
        ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
ORDER BY 'Table Name'

Let us understand this with an example. In the below example, creating a demo database SQLHINTSDEMO with three tables Customer, Orders and Employee. And then search all the tables in the SQLHINTSDEMO database which have column name like CustomerId. It should return two table names Customer and Orders as both have column with column name like CustomerId.

CREATE DATABASE SQLHINTSDEMO
GO
USE SQLHINTSDEMO
GO
CREATE TABLE dbo.Customer(CustomerId INT, 
 CustomerName VARCHAR(100))
GO
CREATE TABLE dbo.Orders(OrderId INT,CustomerId INT)
GO
CREATE TABLE dbo.Employee(EmployeeId INT, 
 EmployeeName VARCHAR(100))
GO
SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
 INNER JOIN sys.columns c
 ON c.object_id = t.object_id
WHERE c.name like '%CustomerId%'
ORDER BY 'Table Name'
GO

RESULT

Find all tables that have specified column name in Sql Server

How to Calculate Age in Sql Server

Scripts, Sql Server, Tips/Tricks,

Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:

Script which gives Wrong Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
     GETDATE() 'Current Date',
 DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'

Incorrect Result:

Date of Birth   Current Date                Age in Years
-------------   -----------------------     ------------
1980-12-29      2012-04-26 09:46:21.833     32
(1 row(s) affected)

Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.

To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:

Script which gives Correct Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
   DATEDIFF(YEAR,@DOB,GETDATE())
     -
   (CASE
      WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
            >  GETDATE() THEN 1
      ELSE 0 END)
   'Age in Years'

Correct Result:

Date of Birth   Current Date              Age in Years
-------------   -----------------------   ------------
1980-12-29      2012-04-26 10:23:05.690   31
(1 row(s) affected)

Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.