Loading ...

SQL: How to convert DateTime to Date | CodeAsp.Net

SQL: How to convert DateTime to Date

 /5
0 (0votes)

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.

GO
CREATE TABLE [DateTable] 
    (
      [ID] INT IDENTITY,
      [Date] DATETIME
    )
--Let's add some data
GO
INSERT  INTO [DateTable]
SELECT DBO.[GenerateRandomDateTime]
('2010-10-28 11:21:13.937','2012-10-30 11:23:13.937')
GO 50

SELECT * FROM [DateTable]
GO

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.

 

Comments (no comments yet)

Top Posts