[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


convert string to date and time

Posted on 2006-04-25
Medium Priority
Last Modified: 2012-06-21
Hi -

Very new to perl - have searched the questions and can't find an answer I can understand :-)

I have two varchar fields in my mysql db that contain a date and a time as strings in the following formats:

date field looks like 04/25/06

time field looks like 9:45 AM

Question is -

How can I convert these text strings into actual dates and times that perl can work with - for date and time manipulations -

thanks in advance -

Question by:rwinnick
LVL 11

Expert Comment

ID: 16535706
It depends on what module you use, I use Time::ParseDate
the following code would print me 2006042521:45:00

$d = "04/25/06";
$t = "9:45 PM";
print &ParseDate("$d $t");
LVL 48

Expert Comment

ID: 16539299
What sort of date/time manipulations do you want to do?

Author Comment

ID: 16539857
Hi -
I am trying to "fix" some pre existing perl code that I inherited which doesn't work :-)

The problem goes like this :-)

I have a date field which has a text string like 04/25/06
I then have a time field which has a text string like 6:45 PM
I need to take these date and time strings and turn them into a time stamp field using local server time (which is PST)
I then need to adjust this timestamp to reflect the timezone the client is in - which is represented in another field as a one digit offset (0,1,2,3)

Up till now I have tried to get the year, month, day, hour, min, and am/pm by using substr() and adjusting for string length 6:45 PM vs 10:45 AM

I have then tried to use timelocal() to create the time stamp

and then try to add 12 hrs if time is pm -

I will then have to compare this timestamp to local server time and decide if an event should happen if the timestamp is less than local time -

Am getting results but they are way off -

I'm sure there has to be a more elegant and correct method of accomplishing this :-)

But after more hours than I care to remember I stuck!

thanks -

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 16539864
Hi Again -

By the way I tried the time::parsedate - but get an error msg and it can't find the module

LVL 85

Accepted Solution

ozo earned 2000 total points
ID: 16539907
$d = "04/25/06";
$t = "9:45 PM";
($M,$D,$Y) = split'/',$d;
($h,$m,$p) = $t=~/(\d+):(\d+)\s*(PM)?/i;
$h += 12 if $p;
use Time::Local;
$timestamp = timelocal(0,$m,$h,$D,$M-1,$Y+2000-1900);

Author Comment

ID: 16540219
Hi ozo -

That is soooo cool and elegant - and it works!!

Not only have you helped me with this problem - you've shown me a standard to work towards -

thanks -


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question