?
Solved

Changing field from text to date in Excel 2010 SQL

Posted on 2013-05-22
7
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 49

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 49

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 1500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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