• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

How can I convert this to date only

SELECT * FROM (SELECT  Distinct c.CustomerID,  e.Name, o.OrderID, o.CreatedOn, oi.Quantity,

I would like to convert or format this date,time to date only.

38      Oregon      1      2009-06-27 08:51:43.960      1      Oregon      0.264      0.000      0.176880      0.000000      0.67            .77             14      Name: Dennis West City: Eagle Point
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT CONVERT(varchar(10), o.CreatedOn, 120 ) CreatedOn
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT * FROM (SELECT  Distinct c.CustomerID,  e.Name, o.OrderID, CONVERT(varchar(10), o.CreatedOn, 120 ) CreatedOn , oi.Quantity ) a
Kevin CrossChief Technology OfficerCommented:
Just note that using convert you are changing the date to a string; therefore, if you still want to treat this value as a date you may want to consider what your need for removing time is.  If you need that just to have records created different times throughout a day to group together, then you can also use date functions like this to make the dates all as of midnight of date.

FROM (SELECT  Distinct c.CustomerID,  e.Name, o.OrderID, DATEADD(d, 0, DATEDIFF(d, 0, o.CreatedOn)) CreatedOn , oi.Quantity ) a
Chris LuttrellSenior Database ArchitectCommented:
I agree with mwvisa1.
Also, why are you doing
Select * from (Select Distinct ...
This query raises red flags to me, maybe you should describe what you are doing and someone here can offer some good SQL practices advice.
westdhAuthor Commented:
Solve it..Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now