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

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
0
GaylordIndusties
Asked:
GaylordIndusties
  • 3
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
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
 
Jerry PaladinoCommented:
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
 
GaylordIndustiesAuthor Commented:
Thanks for your help.  Would of preferred not to add another column, but it works and that is what matters.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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