How to get only Date Part from Sql Server DateTime Function

In most of the cases we need omit Time part from a Date. Since its a very common selection from Sql Server DateTime, Sql Server provides us lots of way to select Date part only from DatTime datatype. In this article i will show you some ways that we can achieve only Date part from a Date. This example is very useful when we use Between clause in two different Dates.

When we need to get Date Part only then actually we need to use SQL Server Convert function. The Convert function has 3 arguments. Datatype, Expression & Style.

Sql Server Convert DateTime Function Syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Note: This approach will work in any version of SQL Server.

Where Style is a numeric number. We can format the Date Part of a date using the style argument.

Some example using Convert Function:

SELECT CONVERT(VARCHAR(11), GETDATE(), 0) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 1) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 2) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 3) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 4) 'Only Date Part'

Output:
SQL Server Date Part Example 1

Example SQL 2:

SELECT CONVERT(VARCHAR(11), GETDATE(), 100) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 101) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 102) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 104) 'Only Date Part'
SELECT CONVERT(VARCHAR(11), GETDATE(), 105) 'Only Date Part'

Output:
SQL Server Date Part Example 2

List of Convert Funtion Style:

2 Digit Year 4 Digit Year Example
1 101 mm/dd/yy, mm/dd/yyyy
2 102 yy.mm.dd, yyyy.mm.dd
3 103 dd/mm/yy, dd/mm/yyyy
4 104 dd.mm.yy, dd.mm.yyyy
5 105 dd-mm-yy, dd-mm-yyyy
6 106 dd mon yy, dd mon yyyy
7 107 Mon dd, yy, Mon dd, yyyy
10 110 mm-dd-yy, mm-dd-yyyy
11 111 yy/mm/dd, yyyy/mm/dd
12 112 yymmdd, yyyymmdd

Now if you again Convert the Varchar output to Date Time then you will get 00 hour, Minute & second. Like:

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) 'Only Date Part',CONVERT(DATETIME,(CONVERT(VARCHAR(12), GETDATE(), 107))) 'Only Date Part 2'

Output:
SQL Server Date Part Example 3

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code