The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server Dates from Perl

Hoping someone can help me with this. I'm terrible at Perl and have an old script I need to update.

I have a perl script that pulls an expiration date from a SQL server 2000 and evaluates wether or not the date has passed or not. The query works and returns the date, but I can't get the evaluation to work properly.

SQL Server returns the date like this:

2006-07-25 00:00:00.000

And Perl (using ctime();) returns the date like:

Thu Jul 13 14:07:53 2006

I run the evaluation like this:

if ($CurrentDate>$SQLDate)

But it doesn't work? Any ideas??

Thanks!!!
Bret
Bret Send private email
Thursday, July 13, 2006
 
 
I've done this with MySQL using the date and time functions (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) I imagine that SQL server has something similar. Or if you pull time timestamp as a unix time (seconds since epoch) you can then compare it using time() since they are both just large integer numbers.

bcl
Brian C. Lane Send private email
Thursday, July 13, 2006
 
 
Probably some Perl variable typing issue where they aren't both going into date types.  Perl isn't strongly typed...

You can always have the SQL Server stored procedure or query return whether the date has passed (GETDATE() in T-SQL).
Cade Roux Send private email
Thursday, July 13, 2006
 
 
You can have SQL format the date however you want.

So in SQL it's a date, but you need to have it coming out of SQL formatted so you can get it to match up with the format Perl uses (just a guess). In Perl can you cast the date coming from SQL into a perl date (cant remember)
D in PHX Send private email
Friday, July 14, 2006
 
 
Perl, per se, has no "Date" or "Time" types.

If you have to do any significant operation involving times, intervals, dates etc., you better use a specific module.

Have a look at this: http://www.perl.com/lpt/a/2003/03/13/datetime.html for pointers.
Paolo Marino Send private email
Friday, July 14, 2006
 
 
Frankly, I'd probably do this inside the database. In Oracle syntax

SELECT CASE WHEN exp_date < Sysdate THEN 'Expired'
      ELSE 'Valid' END as status,
...
FROM warranties
WHERE...

But perhaps the path of least resistance is to bash one time string into the format the other expects, e.g.

#!/usr/bin/perl -w-

my $arbitrary_date1 = '2006-07-13 12:15:14:0000';
my $arbitrary_date2 = '2006-07-14 14:30:30:0000';


my ($sec, $min, $hour, $mday, $mon, $year) = localtime(time());

my $datestring = sprintf('%d-%02d-%02d %02d:%02d:%02d:0000',
                        $year + 1900, $mon + 1, $mday, $hour, $min, $sec);

print 'It is now ', ($arbitrary_date1 gt $datestring) ? 'before' : 'after',
  " $arbitrary_date1\n";

print 'It is now ', ($arbitrary_date2 gt $datestring) ? 'before' : 'after',
  " $arbitrary_date2\n";
George Jansen Send private email
Friday, July 14, 2006
 
 
use Date::Manip or DateTime modules
Tom Vu
Sunday, July 16, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz