Category Archives: Tips/Tricks

How to get Date Part only from DateTime in Sql Server

Sql Server, Tips/Tricks, , , , , , , ,

Many times we come across a scenario where we need to get Date Part only from DateTime in Sql Server. There are multiple ways of doing this, here I am listing out few of them:

1) Below approach works in Sql Server 2008 and above:

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013-07-14

2) Below approaches works in all the versions of Sql server

i) Get Date Part only from DateTime using CONVERT function
Example 1:

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) 'Date Part Only'

RESULT:
Date Part Only
--------------
20130714

Example 2:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013/07/14

The results of the above query is of type VARCHAR, if we want the result to be of type DATETIME we can write a query like below:

Example 1:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 112)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 111)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

ii) Get Date Part only from DateTime using DateTime functions

From performance perspective this is the better approach instead of first converting DATETIME to VARCHAR and then VARCHAR to DATETIME.

Example 1:

SELECT DATEADD(dd, 0, 
        DATEDIFF(dd, 0, GETDATE())) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

DECLARE  @DatePartOnly DATETIME
SEt @DatePartOnly = DATEDIFF(DD, 0, GETDATE())
SELECT @DatePartOnly 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iii) Get Date Part only from DateTime using FLOOR and CAST functions

As we know Sql Server internally stores DATETIME as two 4-byte integers. First 4-byte stores the elapsed number days since SQL Server’s DATETIME type’s start date 19000101.The Second 4-bytes Store the Time of Day i.e. clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds.

So with above said internal storgae of the DATETIME, we can first convert the DATETIME to DECIMAL, then from decimal part ignore the fractional position and get only the integer part. Finally convert the integer to DATETIME as shown below:

SELECT CAST( -- Convert the integer to DATE
         FLOOR(-- Get largest Integer less than or equal to the decimal value
                CAST(GETDATE() AS DECIMAL(12, 5)) -- Convert DATETIME to DECIMAL
              ) 
         AS DATETIME) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iv) Get Date Part only from DateTime using DATEPART and CONVERT functions

DECLARE @GETDATE AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR(4),DATEPART(YEAR, @GETDATE)) 
       + '/'+ CONVERT(VARCHAR(2),DATEPART(MONTH, @GETDATE)) 
       + '/' + CONVERT(VARCHAR(2),DATEPART(DAY, @GETDATE)) 
         'Date Part Only'

RESULT:
Date Part Only
--------------
2013/7/14

You may like to read the other popular articles on Date and Time:

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 find all dependencies of a table in Sql Server?

DMVS, Sql Server, SQL Server 2008 What's New, Tips/Tricks, , , ,

First of all we shouldn’t use the system stored procedure sp_depends for this as it is not reliable. For more details on sp_depends you can refer to the article sp_depends results are not reliable.

For this in Sql server 2008 new Dynamic Management Function sys.dm_sql_referencing_entities is introduced. This dynamic management function provides all the entities in the current database that refer to the specified table.

Let us understand this with an example. First create a table, then a stored procedure which references the table.

CREATE DATABASE DemoSQLHints
 GO
 USE DemoSQLHints
 GO
CREATE TABLE dbo.Employee
 (Id int IDENTITY(1,1), FirstName NVarchar(50), LastName NVarchar(50))
GO
 INSERT INTO dbo.Employee(FirstName, LastName)
 VALUES('BASAVARAJ','BIRADAR')
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
 AS
 BEGIN
 SELECT * FROM dbo.Employee
 END
GO

Now we can use a script like below to find all entities in the current database that refer to the table dbo.Employee:

SELECT referencing_schema_name, referencing_entity_name, 
 referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities ('dbo.Employee', 'OBJECT')
GO

Result:
dm_sql_referencing_entities
Note: 1) This Dynamic Management Function is introduced as a part of Sql Server 2008. So above script works in Sql Server version 2008 and above.
2) While specifying the table name please include schema name also, otherwise result will not display the dependencies.

You may also like to read my other articles:

How to find all the objects referenced by the stored procedure in Sql Server?

Please correct me if my understanding is not correct. Comments are always welcome.