convert datatime to date only in tsql sql server
There are two date related data types in SQL Server DateTime and SmallDateTime. Manipulating the Date time are very tricky in SQL Server, here are some converstion methods to get the date part only from datetime column.
--MM/DD/YYYY
SELECT CONVERT(char, GETDATE(), 101)
--YYYY.MM.DD
SELECT CONVERT(char, GETDATE(), 102)
--DD/MM/YYYY
SELECT CONVERT(char, GETDATE(), 103)
--DD.MM.YYYY
SELECT CONVERT(char, GETDATE(), 104)
--DD-MM-YYYY
SELECT CONVERT(char, GETDATE(), 105)
--DD MON YYYY
SELECT CONVERT(char, GETDATE(), 106)
-- MON DD, YYYY
SELECT CONVERT(char, GETDATE(), 107)
--MM-DD-YYYY
SELECT CONVERT(char, GETDATE(), 110)
--YYYY/MM/DD
SELECT CONVERT(char, GETDATE(), 111)
--YYYYMMDD
SELECT CONVERT(char, GETDATE(), 112)
--YYYY-MM-DD
SELECT CONVERT(varchar(10), GETDATE(), 120)
To get the day / month or year only from given date you can use Day( ), Month() and Year() builtin functions.
--Returns only DAY part of the date
SELECT Day(GetDate())
--Returns only MONTH part of the date
SELECT Month(GetDate())
--Returns only YEAR part of the date
SELECT Year(GetDate())
You can also use LEFT and RIGHT functions on Year() to get the century only from date or 2 digit year from given date in SQL Server.
To get the century from the year using tsql, the following query should return "20"
SELECT LEFT(Year(GetDate()),2)
To get the two digit year we can use the right function to get only the 2 digit year.
SELECT RIGHT(Year(GetDate()),2)
Check SQL Server books online for more date formats.