Solved

Changing field from text to date in Excel 2010 SQL

Posted on 2013-05-22
7
319 Views
Last Modified: 2013-05-28
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
Comment
Question by:GaylordIndusties
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39189441
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
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 39189565
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
 

Author Comment

by:GaylordIndusties
ID: 39190840
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39190865
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
 

Author Comment

by:GaylordIndusties
ID: 39190910
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
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 39191250
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
 

Author Closing Comment

by:GaylordIndusties
ID: 39202801
Thanks for your help.  Would of preferred not to add another column, but it works and that is what matters.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now