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 );
}
|