|Problems? Is your data what you think it is?|
Converting Julian Dates to Textby finhagen (Sexton)
|on Oct 28, 2008 at 03:16 UTC||Need Help??|
finhagen has asked for the
wisdom of the Perl Monks concerning the following question:
I am attempting to parse Excel spreadsheets and insert the data into mysql. I am actually having quite a bit of success, but one issue I have not been able to address is ingesting dates. The unformatted date in Excel appears to be in the Julian format* (e.g. 10/28/2008 is 39749). There are three problems with that:
1) mysql expects dates formatted as 2008-10-28. I wrote a simple script to convert a standard US date format to mysql format:
2) when I parse the date in the spreadsheet it actually is ingested as 39749 (sigh) so my split join script is of no value.
3) I did some research and it doesn't appear that mysql can convert Julian dates to it's date standard (2x sigh). Therefore, it looks like I need a perl script that can convert a Julian date to a mysql date. I looked on cpan and while there are some modules which address Julian dates, I didn't see anything that would do what I am trying to do. Does anyone have a Julian to mysql date conversion script?
*I typically receive the source spreadsheet via email in Office 97 xls format. I run Windows in a VM on top of a SUSE linux host os. When I save the xls file to my Unix (ext3) filesystem the date seems to distill down to the Julian format. I am running mysql and perl on the linux host and that appears to result in this Julian format issue.