Dates from SQL Query

Question

This is perhaps a very simple question.

I have a very basic query in Excel that pulls in some values from a db on Sql Server 2008 R2.

The date fields in this db are stored as datetime. In this query, I am uninterested in the time part, and so only want to select the date. I have tried using Cast or Convert, however both return the date in sql (yyyy-mm-dd) to Excel. Excel recognizes this as text rather than a date, and so I am unable to do date filters in my table once the data is in Excel.

If I do datepart() in Excel, it shows it as a date and I can filter as expected, however I want the data coming directly from the query to return as a date value that Excel can filter on.

Below is my sample query:

SELECT order_header.oh_id, 
order_header.oh_order_number, 
order_header.oh_datetime, 
convert(date, order_header.oh_datetime, 103) as 'ConvertOrderDate',
cast(order_header.oh_datetime as date) as 'CastOrderDate'
FROM order_header
WHERE order_header.oh_datetime >= '2013-09-01'

When returning this to Excel, this is what is being returned:

Output Image http://img834.imageshack.us/img834/4417/7gbg.png

where you convert/cast to date try changing it to datetime — T I
– T I, Commented Sep 23, 2013 at 13:54
I hate Excel's date recognition: it seems to always guess wrong. If I want text or numeric, it will think it's a date. If I want a date, it won't see it. — Joel Coehoorn
– Joel Coehoorn, Commented Sep 23, 2013 at 15:19

SQLSam · Accepted Answer · 2013-09-23 15:01:23Z

1

I have actually found the answer without doing anything weird :)

SELECT order_header.oh_id, 
order_header.oh_order_number, 
order_header.oh_datetime, 
DATEADD(dd, DATEDIFF(dd, 0, order_header.oh_datetime), 0) as 'OrderDate'
FROM order_header
WHERE order_header.oh_datetime >= '2011-09-01'

This returns as a datetime but with 00:00 in the time, so I can simply format this out in Excel.

answered Sep 23, 2013 at 15:01

SQLSam

5371 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alerya Over a year ago

Not all Cultures can 'eat' your dashed time. '20110901' a little better

Collectives™ on Stack Overflow

Dates from SQL Query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related