Extract only the Date from DateTime in SQL Server

Normaly extarcting date only from DATETIME is commaon and serious problem in SQL SERVER.
Here i have given some common methods to extract on the date from datetime

Method 1

------------

SELECT
CAST
(
FLOOR(CAST(GETDATE() AS FLOAT))
AS DATETIME
)

Method 2--but it is not correct

------------------------------------
This is similar to first method but we have used INT instead of FLOAT . But it will seldom correct. It will not give you the exact date of what you expecting.
So better not to use INT.

SELECT
CAST
(
FLOOR(CAST(GETDATE() AS INT))
AS DATETIME
)

Method 3

----------
i would recommend this method to extract inly date though there are many methods. because this method is very simple and easy.


SELECT
CAST
(
STR(DAY(GETDATE()))+ '/' +
STR(MONTH(GETDATE())) +'/'+
STR(YEAR(GETDATE()))
AS DATETIME
)

Method 4

----------
In this we have used CONVERT function with 113 date type.

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 113))

Method 5

----------
Here we have used DATEDIFF and DATEADD functions together to extract.get the different between specified date and 0 then again add o with output value of DATEDIFF

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)


Method 6

----------
This is also similar to previous method. But here we have not used DATEADD instead we have used CAST.

SELECTCAST(DATEDIFF(dd,0,GETDATE() as datetime)

Comments

Post a Comment

Popular posts from this blog

Use of Begin, Commit, Rollback Transactions in SQL Server

Image
The SQL Server provides very useful feature which is Begin, Commit, Rollback Transaction. When we use Begin Transaction before we use DML Queries, we can Commit or Rollback that Transaction after the confirmation. This is very useful if you update anything wrongly then you can rollback that transaction. For example,As shown below, I am trying to update the NodeID column data to 5 from 1. begin transaction update DownLoad_Data set NodeID=5 But after I updated, You can check whether you have been updated properly. But here, I realised I did not mention the Where clause. select * from DownLoad_Data So I have to rollback this transaction. For that I can use Rollback Transaction since I used Begin Transaction. Rollback transaction So again I changed the Query and run it. Begin transaction update DownLoad_Data set NodeID=5 where RecNo=1 Still you can check whether have been updated properly. If it is updated correctly then, Run the Commit Transaction to make all updates permanently. Commit...
Read more

SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.
Read more