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! :)

Solution 1

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 

Solution 2

try the following as there will be no varchar conversion

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

Solution 3

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'

Solution 4

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

Solution 5

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

View more solutions