As others have already mentioned, it really would be better (for both you and us) if you posted code that shows us your attempt at solving the problem. Nonetheless, I wrote this sub a while ago, and I decided to post it here in the event that you or someone else may find it useful. There are plenty of references to help you figure out how to use DBI to insert the data into the database, so you're on your own for that part (if you get stuck feel free to post another question, but show us what you've tried when you do).
The sub takes a date/time string as an argument and uses the Date::Manip module to parse and calculate the corresponding Microsoft date and time serial numbers.
use strict;
use warnings;
use Date::Manip;
Date_Init( "TZ=CST6CDT" ); # see Date::Manip docs about this
my $datestring = 'Dec 1, 2005 11:17:20 PM';
my ( $date_sn, $time_sn ) = calc_serial_numbers( $datestring );
print "date SN = $date_sn\n"; # 38687
print "time SN = $time_sn\n"; # 0.97037037
sub calc_serial_numbers
{
# This sub takes a string containing a date and time, and
# calculates the corresponding Microsoft date and time serial
# numbers. Note there is a bug in the MS calculation (for
# historical reasons MS calculates 1900 as a leap year; source
# is Date::Calc docs) which necessitates adding an extra day to
# the date serial number (therefore it is calculated based on
# 12-31-1899 rather than 1-1-1900). These serial numbers are
# suitable for storing in MS Access as a date/time field, and
# can also be used in MS Excel.
my ( $datetimestring ) = @_;
# calculate the number of days since 12-31-1899
# (or 1-1-1900, according to MS)
my $parsed_date = ParseDate( $datetimestring );
my $date_delta = DateCalc( 'Dec 31, 1899', $parsed_date );
my $date_sn = Delta_Format( $date_delta, 0, ( '%dh' ) ) + 1;
# calculate the fraction of the current day, in seconds
# 60*60*24 = 86400 seconds per day
my $daystart = Date_SetTime( $datetimestring, '00:00:00' );
my $time_delta = DateCalc( $daystart, $parsed_date );
my $num_sec = Delta_Format( $time_delta, 0, ( '%sh' ) );
my $time_sn = sprintf( "%.8f", $num_sec/86400 );
return( $date_sn, $time_sn );
}
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.