Changing field from text to date in Excel 2010 SQL

With in Excel 2010 under Data/Connections/Connection Properties/Definition tab/Command text, I am trying to convert the Quote.QuoteDate from text to date.  Is there a way?  The code is below:

SELECT Quote.QuoteID, Quote.QuoteDate, Quote.ProjectName, Quote.ProjectLocation, Quote.CreateDate, Quote.NetPrice, Quote.RevLev, Quote.SalesRegion, Quote.TerrRep, Quote.Budget
FROM Projects.dbo.Quote Quote
GaylordIndustiesAsked:
Who is Participating?
 
Jerry PaladinoConnect With a Mentor Commented:
Two possible options.

1) force the users to enter a consistent valid date format such as mm/dd/yyyy into the original SQL data.

2) Add a helper column to the right of your query output in the Excel worksheet that will convert the information in Quote.QuoteDate to an actual numeric date.  Depending on the values in the QuoteDate column you may be able to use IF statements and the DATEVALUE function.   I would have to see the actual column of data to make a more specific recommendation.
0
 
PortletPaulfreelancerCommented:
well, yes, probably, at a guess; there is no detail of what is in that column Quote.QuoteDate

however; if it should contain things like "mystery" then the answer would be no

more information please?
0
 
Jerry PaladinoCommented:
Also check the format of what is being returned in the Quote.QuoteDate column.   If it is text that Excel cannot resolve into a numerical date then that could be causing the issue.  Excel will try to determine the format of a QueryTable column based on the first 8-10 rows.  If they contain blanks or text that cannot be resolved to a numeric date then the column will be text.  As the previous post mention... more information is needed.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
GaylordIndustiesAuthor Commented:
The Quote.QuoteDate is a varchar(50) and not a date field in the original SQL table.  I am linking to it using the Excel Import function using query and ODBC.
0
 
PortletPaulfreelancerCommented:
good information, but what does the data look like?
is it stored as yyyy-mm-dd hh:mm:ss for example?
are there exceptions?
is it stored (horror) as something like mmm dd yy (Jun 11 12)

the point is that varchar columns could be anything (up to the 50 char length)
I am trying to convert the Quote.QuoteDate from text to date.  Is there a way?
to answer this requires knowing something about that varchar data
0
 
GaylordIndustiesAuthor Commented:
For the most part it is mm/dd/yyyy (ex. 3/7/2011), but there are few other formats as well.  Such as blanks and other various formats that users keyed in manually.
0
 
GaylordIndustiesAuthor Commented:
Thanks for your help.  Would of preferred not to add another column, but it works and that is what matters.
0
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.

All Courses

From novice to tech pro — start learning today.