CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Converting SQL date time to DateTime

 



PapaGeek
User

Mar 19, 2014, 12:37 PM

Post #1 of 6 (1587 views)
Converting SQL date time to DateTime Can't Post

Im using an ODBC connection to a Microsoft Access database and when I query a date time field I get back a string like 2014-03-14 00:00:00. I want to work with this date in my Perl ap. I need to convert it into a Perl DateTime object. I have found multiple examples of how to convert a DateTime object into SQL format, but nothing on how to convert SQL format to DateTime.

The last line of this code does not compile, neither did the other 6 tries for the last line. Im sure the answer is simple, but how do you convert a SQL returned DateTime string into a Perl DateTime object?



Code
use DateTime; 
use DBI;

my $SQLstring = "2014-03-14 00:00:00";

my $dt = DateTime::format::DBI($SQLstring);



FishMonger
Veteran / Moderator

Mar 19, 2014, 1:06 PM

Post #2 of 6 (1585 views)
Re: [PapaGeek] Converting SQL date time to DateTime [In reply to] Can't Post

Use the split function to split out the individual fields in the returned timestamp and feed those values to the DateTime constructor.


Code
my ($yr, $mon, $day, $hr, $min, $sec) = split /[- :]/, $SQLstring; 

my $dt = DateTime->new(
year => $yr,
month => $mon,
day => $day,
hour => $hr,
minute => $min,
second => $sec,
);



PapaGeek
User

Mar 19, 2014, 1:40 PM

Post #3 of 6 (1581 views)
Re: [FishMonger] Converting SQL date time to DateTime [In reply to] Can't Post

Thanks for the reply,

So basically there is no real direct way to convert SQL directly into DateTime. It has to be done with multiple lines of code! I was afraid of that!


Chris Charley
User

Mar 19, 2014, 1:41 PM

Post #4 of 6 (1578 views)
Re: [PapaGeek] Converting SQL date time to DateTime [In reply to] Can't Post

Another way using DateTime::Format::Strptime...


Code
use DateTime::Format::Strptime; 

my $parser = DateTime::Format::Strptime->new(pattern => "%F %T");

my $SQLstring = "2014-03-14 00:00:00";

my $dt = $parser->parse_datetime($SQLstring);

print $dt;

prints: 2014-03-14T00:00:00

$dt is now a DateTime object and you may perform DateTime operations on it.


(This post was edited by Chris Charley on Mar 19, 2014, 1:45 PM)


FishMonger
Veteran / Moderator

Mar 19, 2014, 1:55 PM

Post #5 of 6 (1569 views)
Re: [PapaGeek] Converting SQL date time to DateTime [In reply to] Can't Post

It's only 1 additional line, and is also the same number of lines that Chris uses. I just split the object assignment across multiple lines for readability/maintainability.

It also doesn't require the use of another module.

I haven't tested it, but if that 1 line is too much, then you might be able to combine them into a single compound statement by using a hash slice.


(This post was edited by FishMonger on Mar 19, 2014, 1:58 PM)


Zhris
Enthusiast

Mar 22, 2014, 6:29 PM

Post #6 of 6 (1402 views)
Re: [PapaGeek] Converting SQL date time to DateTime [In reply to] Can't Post

In order to convert an SQL timestamp into a DateTime object, there will have to be a function somewhere which actually performs this conversion, regardless of whether the code is already available as part of a module, or you write it yourself. If you do write it yourself, then you'll probably contain it in a re-usable function, therefore performing the conversion in one line of code.


Code
	 
my $date_time = sql_timestamp_to_datetime( $sql_timestamp );


Looking at DateTime::Format::DBI, there is a parse_datetime method which looks handle your requirement.


Code
my $date_time = $date_time_format_dbi->parse_datetime( $sql_timestamp );


In one web based framework I use, I can provide a table specification which defines any date columns and automatically converts them to DateTime objects upon select. The opposite can be achieved on insert, update etc. Therefore the conversion happens implicitly in the background.

Chris


(This post was edited by Zhris on Mar 22, 2014, 6:34 PM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives