Data connections in Excel 2007, remove time from DATE parameter

Question

I am new to SQL, and have recently made connections between two Excel spreadsheets.  SQL automatically adds a time to a column of dates on import, and I need to remove the time from the output.

I know there are a lot of examples on how to do this, but my SQL programming is less than basic.  I need to adapt code that was created using a query wizard so time is removed from dates. 

I have seen solutions such as..

CAST(GETDATE() AS date)

and...

declare @d datetime
select @d =  '2008-12-1 14:30:12'

where tstamp >= dateadd(dd, datediff(dd, 0, @d)+0, 0)
and tstamp < dateadd(dd, datediff(dd, 0, @d)+1, 0)

..but I am unsure of how to incorporate them into my code.

Here is the code for my data connection that was generated by the Query Wizard:

SELECT `Sheet1$`.Date, `Sheet1$`.Customer, `Sheet1$`.List
FROM `C:\Documents and Settings\User\Desktop\T1.xlsx`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Date=[?])

I don't want to resort to trimming the output with a new column in Excel.  Any help is much appreciated!

Answer 1

http://www.techonthenet.com/excel/questions/remove_time.php   

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

I appreciate the response but I am trying to accomplish this before the data is transitioned into Excel, by altering the SQL query.

I'm pretty good with Excel but don't want to add a bunch of conversion columns for my data, if I can avoid it.

Answer 3

Hello,

Is this what you are looking for?

--Date and time
SELECT GETDATE()
--Just Date
SELECT CONVERT(CHAR(10), GETDATE(), 101)

-Sean

Answer 4

I believe so, but I am really not sure how to integrate the Just Date method into my existing code. 

Would it look like this?

SELECT CONVERT(CHAR(10),GETDATE(),101)`Sheet1$`.Date, `Sheet1$`.Customer, `Sheet1$`.List
FROM `C:\Documents and Settings\User\Desktop\T1.xlsx`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Date=[?])

Answer 5

Hello,

What about this?

SELECT CONVERT(CHAR(10),`Sheet1$`.Date,101) AS [Just_Date], `Sheet1$`.Customer, `Sheet1$`.List
FROM `C:\Documents and Settings\User\Desktop\T1.xlsx`.`Sheet1$` `Sheet1$`
WHERE CONVERT(CHAR(10),`Sheet1$`.Date,101) = CONVERT(DATE, '04/05/2012', 101)

-Sean

Answer 6

Produces error - undefined function CONVERT in expression -

I am running Windows 7, but I am checking on what SQL this query is running through.  Probably going to have to use another method though.

Answer 7

This is one of my favorite resources for SQL Server Date formatting:

http://www.sql-server-helper.com/tips/date-formats.aspx

Remember, it is just a format, so the underling data doesn't change.  The only thing that changes is how this data is rendered, or represented.

---

Ryan Shuell

Answer 8

Thank you.  And for anyone who is trying to use an in cell date reference as a parameter for their MS query in Excel, it MUST be in text format.

Any other format will produce an error and drive you crazy trying to alter your query.  Ultimately, I just formatted the columns of data to be date only in Excel which is an easy fix, but had to format the in cell parameter as TEXT.