> This is probably something really simple that I'm missing,
> but I was wondering if there is some way to get SQL to return
> a date field in the same format as Perl's time() or
> localtime(). Right now I keep getting a string like this:
> "1/20/2003 10:37:17 AM", which doesn't help me when it comes
> to calculating dates or scanning through an excel spreadsheet, etc.
You are getting the date/time back in the ODBC standard format.
If you have to maniupulate/compare/etc times on the Perl side, then the
most generic way to handle it is to use a date package in Perl. You
should probably look at Date::Manip for example.
YMMV with each one, so try a few of the date packages (or look at the
POD headers first to see what they do).
If you can handle it on the db side (convert, to_char, etc) and compare
on the server/database side inside your query (i.e. select * from TASKS
where CLSDDATE IS NULL), then you are a step up on dealing with the
dates. Depending upon the database you are using, you should be able to
do date math on the server side. For example: select
avg(CLSDDATE-OPENDATE) from TASKS where CLSDDATE is not null.
It's going to depend upon what you need and if your driver/database can
handle it. If you are using Excel, you might have to do more work
yourself. I've had to do that with, for example, Lotus Notes ODBC
driver. Any "real" where clause caused the query to fail. So, I ended
up pulling everything to the client to process or uploading it to Oracle
to process and report on it.
Jeff
>
> Here's a little bit of my code:
>
> #############################################
>
> use strict;
> use DBI;
> use warnings;
> no warnings qw(uninitialized);
>
> my $SQLdbh = DBI->connect("dbi:ODBC:Sys_Trackit",'','')|| die
> "$DBI::errstr\n"; my $sth = $SQLdbh->prepare(qq{SELECT * FROM
> TASKS}) || die "$DBI::errstr\n"; $sth->execute();
>
> while(my $ref = $sth->fetchrow_hashref()){
> $open_date = $ref->{OPENDATE};
> $close_date = $ref->{CLSDDATE};
> #do something
> }
>
> #############################################
>
> and yes, for those of you that recognize it, I am using
> TrackIt. It's awful, but it's all I've got.
>
