SQL datetime format to date only

Question

I am trying to select the DeliveryDate from sql database as just date. In the database, i am saving it as datetime format. How is it possible to get just date??

SELECT Subject, DeliveryDate 
from Email_Administration 
where MerchantId =@ MerchantID

03/06/2011 12:00:00 Am just be selected as 03/06/2011..

Thanks alot in advance! :)

What RDBMS and Version? And is your desired format `dd/mm/yyyy` or `mm/dd/yyyy` — Martin Smith, Mar 07 '11 at 16:20
My desired format us mm/dd/yyyy. I am a newbie for SQL, i have no idea how i can find the RDBMS type and version. All i know is SQL server 2005. — challengeAccepted, Mar 07 '11 at 16:29
Possible duplicate of http://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime and others. — goodeye, Aug 31 '13 at 03:29
Possible duplicate of [How to remove the time portion of a datetime value (SQL Server)?](http://stackoverflow.com/questions/2775/how-to-remove-the-time-portion-of-a-datetime-value-sql-server) — Michael Freidgeim, Aug 31 '16 at 05:49

score 35 · Accepted Answer · answered Mar 07 '11 at 16:27

After perusing your previous questions I eventually determined you are probably on SQL Server 2005. For US format you would use style 101

select Subject, 
       CONVERT(varchar,DeliveryDate,101) as DeliveryDate
from Email_Administration 
where MerchantId =@MerchantID

score 22 · Answer 2 · edited Nov 13 '12 at 15:54

22

try the following as there will be no varchar conversion

SELECT Subject, CAST(DeliveryDate AS DATE)
from Email_Administration 
where MerchantId =@ MerchantID

edited Nov 13 '12 at 15:54

khr055

28,690
16
36
48

answered Nov 13 '12 at 15:36

efatihan

347
2
8

1

This is better than CONVERT(varchar,FIELD,103) as [DD/MM/YYYY] – Radamanf Jun 03 '16 at 12:37
1

What I did was `LEFT(CONVERT(VARCHAR, DeliveryDate, 126), 10)` because I needed the date in the format `yyyy-mm-dd` – Aaron C Jul 28 '18 at 19:33

score 4 · Answer 3 · answered Mar 07 '11 at 16:29

4

With SQL server you can use this

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY];

with mysql server you can do the following

SELECT * FROM my_table WHERE YEAR(date_field) = '2006' AND MONTH(date_field) = '9' AND DAY(date_field) = '11'

answered Mar 07 '11 at 16:29

Theresa Forster

1,914
3
19
35

score 2 · Answer 4 · answered Jun 16 '21 at 14:59

Create a function, do the conversion/formatting to "date" in the function passing in the original datetime value.


CREATE FUNCTION dbo.convert_varchar_datetime_to_date (@iOriginal_datetime varchar(max) = '')
RETURNS date
AS BEGIN
    declare @sReturn date

    set @sReturn = convert(date, @iOriginal_datetime)

    return @sReturn
END

Now call the function from the view:


    select dbo.convert_varchar_datetime_to_date(original_datetime) as dateOnlyValue

Manish · Answer 5 · 2011-09-28T10:34:21.440

2

SELECT Subject, CONVERT(varchar(10),DeliveryDate) as DeliveryDate
from Email_Administration 
where MerchantId =@ MerchantID

edited Sep 28 '11 at 10:34

answered Sep 28 '11 at 07:50

Manish

517
1
3
19

score 1 · Answer 6 · edited Dec 14 '22 at 16:57

1

if you are using SQL Server use convert

e.g. select convert(varchar(10), DeliveryDate, 103) as ShortDate

more information here: http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

edited Dec 14 '22 at 16:57

Eray Balkanli

7,752
11
48
82

answered Mar 07 '11 at 16:27

SteveB

1,474
1
13
21

SQL datetime format to date only

6 Answers6

Linked