[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Changing field from text to date in Excel 2010 SQL

Posted on 2013-05-22
7
Medium Priority
?
328 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

650 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