In this blog I will show how to convert datetime to date in SQL. I have seen many people asking on forums regarding the same therfore I decided to write a blog on this. The logic of doing is simple, just strip the datetime upto 10 characters and convert it to date string of your desired date format. In this blog I have used 120 as the style of the date. You can get information on these formats and styles from here CAST and CONVERT . Now let's come to the sample and test it.
DECLARE @DateTime DATETIME
SELECT @DateTime = '2010-10-28 11:21:13.937'
DECLARE @DateOnly VARCHAR(10)
SELECT @DateOnly = CONVERT(VARCHAR(10),@DateTime ,120)
SELECT @DateOnly AS [DateOnly]
Below is the output:
Let's play with second example in which I have created a dummy table and inserted 50 random records of datetime type.
CREATE TABLE [DateTable]
[ID] INT IDENTITY,
--Let's add some data
INSERT INTO [DateTable]
('2010-10-28 11:21:13.937','2012-10-30 11:23:13.937')
SELECT * FROM [DateTable]
You can get the script of [GenerateRandomDateTime] function used above from SQL: Generate random datetime for a given range . Below is the output of the above script and you can see the records have been inserted.
Now let's take out date from these datetimes:
SELECT CONVERT(VARCHAR(10), [Date], 120) AS [Date] FROM [DateTable]
Below is the output after running the above script:
Do let me know your feedback, comments.