Sql Datetime Format to Date Only

SQL datetime format to date only

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 

Convert DateTime into Date only, inside of the SELECT SQL statement

There is no format in a DateTime or Date object. That only comes with the display of the data.

You were very close with the Convert, but needed just the output format.

CONVERT(VARCHAR(10), DATEFROM, 101)

This will output the DATEFROM column as mm/dd/yyyy

How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

• No varchar<->datetime conversions required
• No need to think about locale

select where date (only date from datetime)

Several solutions. Cast datetime to date this eliminates the time

 Select * from table where cast (column as Date) = '1 jan 2017'

second, use date diff function

 Select * from table where datediff(day, column, '1Jan2017') = 0

third, strip time portion using datediff and dateadd

 Select * from table 
 where dateadd(day, datediff(day, 0, column), 0) = '1Jan2017'

also, compare to midnight at start and end of the day

 Select * from table 
 where column >= '1 Jan 2017'
    and column < '2 jan 2017'

Last solution is only one that allows use of index on the date column

EDIT ( to address concerns raised by Joel in comments):
Please understand that the C# code you are using to pass this query to the Server is vulnerable to what is called "SQL Injection". If your application is constructing this SQL from input from untrusted users, this is an invitation to disaster. As Joe; suggests in his answer below, the C# code should be modified to use command objects with defined parameters, instead of dynamically constructed SQL. The SQL statement itself, however can still be any of the solutions suggested above.

How to convert datetime to date only (with time set to 00:00:00.000)

For SQL Server 2005 and below:

CONVERT(varchar(8), @ParamDate, 112)    -- Supported way

CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME)   -- Unsupported way

For SQL Server 2008 and above:

CAST(@ParamDate AS DATE)

For SQL Server 2022 and above:

DATETRUNC(d, @ParamDate)

Convert a SQL Server datetime to a shorter date format

Have a look at CONVERT. The 3rd parameter is the date time style you want to convert to.

e.g.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format

How to query DATETIME field using only date in Microsoft SQL Server?

use range, or DateDiff function

 select * from test 
 where date between '03/19/2014' and '03/19/2014 23:59:59'

or

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

1. If you have control over the database schema, and you don't need the
   time data, take it out.

2. or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...

Alter table Test
Add DateOnly As
DateAdd(day, datediff(day, 0, date), 0)

or, in more recent versions of SQL Server...

Alter table Test
Add DateOnly As
Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)

then, you can write your query as simply:

select * from test 
where DateOnly = '03/19/2014'

---

Related Topics