Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
AM/PM causing problem when sorting by date in Excel using data exported from SQL DB?
I have a web-based software application that allows users to export SQL database data to Excel. One of the columns is a date/time field that includes AM or PM after the time and the various SQL formats don't put a space before the am/pm (example: 5/23/2008 11:15AM).
Some users would like to be able to sort by the date / time field in Excel. However, Excel date formats always have a space before the am/pm (ie: 11:15 AM) and as a result it does not recognize the SQL-format date and messes up the sort (even when I specifically format the cells as Date in Excel).
I tried inserting a space by hand on a few of the fields in Excel and then they sorted correctly (so that is definitely the issue), but I can't expect users to do that for 100s of rows. They also don't want to use a 24-hour format without the am/pm.
Is there any way to either have SQL format the date with a space before the am/pm or force Excel to be able to recognize the SQL date format without the space?
3 Answers
- jimgmacmvpLv 71 decade agoFavorite Answer
Hi,
From your example I am assuming that the time, 11:15AM, is in the data table as a text string, not as a time value.
There are several ways to deal with the problem.
One way is to clean up the data tables and use time values that Excel understands. For example, Excel considers the time of day as a fraction of one day. Noon is .5, for instance. You could convert the text strings to fractions in the data tables.
Another is to completely convert the date and time strings to Excel date and time format. Just read Excel's help about date and time to learn how Excel works with these things.
You don't say what kind of server you're using. SQL server and Microsoft Access both can store dates and times in formats that Excel understands, even if they are native Excel formats. You should clean up the data if you are using either of these databases.
More complicated would be to write a SQL query that parses the time string. You would need to break it at the third character from the right, and concatenate a space. Deliver the concatenated result in your result set.
The easy way is to use a date and time instead of a text string in the data tables.
-Jim Gordon
- five vLv 41 decade ago
Looks like you'll need to reformat the dates before they're dumped into Excel. Depending on what flavor of SQL you're using (instructions can differ for different databases) you should be able to use a "CONVERT" function to change the date format to one that doesn't have the AM/PM characters.
Some references here:
http://www.sqljunkies.ddj.com/Article/6676BEAE-196...
- 5 years ago
try to convert them to unix timestamp format (number of seconds from January 1st, 1970). there should be a function that does this for you (and back from it). then the comparison is easy, you just compare integers, and you can easily do date manipulations (ie 1 day = 86400 seconds)
