1

I have a table Orders, and I am getting order date specification like expiry date etc, from a client/user, but I don't need order expiry time. Below query is with datetime like: 

SELECT 
    orderDate as Order_DateTime,  
    convert(varchar(10),orderDate,120) as Order_Date,
    DATEADD(dd,2,orderDate) as Order_Expiry_DateTime,
FROM 
    [Orders]
WHERE 
    orderId = 55;

The result is: 

Order_DateTime          |  Order_Date   | Order_Expiry_DateTime   
2013-12-19 21:54:00.000 | 2013-12-19    | 2013-12-21 21:54:00.000 
2013-12-19 19:21:00.000 | 2013-12-19    | 2013-12-21 19:21:00.000 
2013-12-19 19:29:00.000 | 2013-12-19    | 2013-12-21 19:29:00.000 
2013-12-24 22:10:00.000 | 2013-12-24    | 2013-12-26 22:10:00.000 
2013-12-25 18:00:00.000 | 2013-12-25    | 2013-12-27 18:00:00.000 
2013-12-24 23:11:00.000 | 2013-12-24    | 2013-12-26 23:11:00.000 
2013-12-24 20:37:00.000 | 2013-12-24    | 2013-12-26 20:37:00.000

Now in following query, I remove time part from Order_Expiry_DateTime.

The result is up to my requirement but can I optimize this query to some extent?

SELECT 
    orderDate as Order_DateTime,  
    convert(varchar(10), orderDate, 120) as Order_Date,  
    convert(varchar(10), (DATEADD(dd, 2, orderDate)), 120) as Order_Expiry_Date   
FROM 
    [Orders]   
WHERE 
    orderId = 55;

The result is good as needed like: 

Order_DateTime          |  Order_Date   | Order_Expiry_Date
2013-12-19 21:54:00.000 | 2013-12-19    |   2013-12-21 
2013-12-24 22:10:00.000 | 2013-12-24    |   2013-12-26 
2013-12-25 18:00:00.000 | 2013-12-25    |   2013-12-27 
2013-12-24 23:11:00.000 | 2013-12-24    |   2013-12-26 
2013-12-24 20:37:00.000 | 2013-12-24    |   2013-12-26

Can 

convert(varchar(10), (DATEADD(dd, 2, orderDate)), 120)

be replaced with something better? I searched but was unable to find one, so please guide me.

Sohail
  • 574
  • 3
  • 21
  • http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype – athabaska Dec 25 '13 at 06:59
  • 1
    But I doubt that performing an extra operation on that Order_Expiry_DateTime field can be called "optimization" – athabaska Dec 25 '13 at 07:00
  • In SQL Server **2008** and newer, you could easily use `CAST(orderDate AS DATE)` to get the date-only portion - but for **2005** I don't think there's much you can do about this expression ... – marc_s Dec 25 '13 at 09:03
  • 1
    @Sohail I think this way is good enough, however performance will be impact more on other things like where clause or how you join etc – vikas Dec 25 '13 at 09:15
  • Your computation is invoked on about 10 rows. It would be hard to even measure any difference, no matter what you do. This is <1% the cost of query execution. – usr Dec 25 '13 at 11:13

1 Answers1

0

Make sure OrderId is indexed and then make sure insterts and updates are not effected on that table if you do add one.

Mark Monforti
  • 463
  • 4
  • 8