Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Using DBI::Sybase having problem with Date format

by yoyomonkey (Initiate)
on May 10, 2007 at 16:14 UTC ( #614678=perlquestion: print w/replies, xml ) Need Help??

yoyomonkey has asked for the wisdom of the Perl Monks concerning the following question:

I was wondering if anyone can help the script below does work but returns dates from the MSSQL server as "Mar 01 2007 12:00AM " I want it to return it as 01/03/07. I have tried changing the DATEFORMAT but nothing seems to work . I have also been trying with a test script which is further below and it returns the date in the wrong format. Can anyone please point me in the right direction?? I have # out the statements that are the problem here I just don't know where i am going wrong with them . Thanks
use vars qw( $connection ); # cached connection # DBI specification of the connection to the database my $DB_PATH = "DBI:Sybase:server=192.168.70.56"; # DB user my $DB_USER = 'user'; # DB user's password my $DB_PASS = 'password'; # TWiki ihook sub initPlugin { return 1; } # TWiki hook; this is run on the text being translated to HTML, and is # your opportunity to expand any tags in that text. sub commonTagsHandler { my($text, $topic, $web ) = @_; $_[0] =~ s/%CurrentReleases%/_show_releases($1)/ge; #substitution +GREP command } # Connect to the DB sub _connect { my $this = shift; unless( $connection ) { $connection = DBI->connect( $DB_PATH, $DB_USER, $DB_PASS, { PrintError => 0, RaiseError => 1, }); } return $connection; } sub _show_releases { my $args = shift; my @headers = map { "*$_*" } qw/ Product Target_Feature_Freeze Targ +et_CCRB Target_DEV_Release Target_RTM/; my $fmt = "| %s | %s | %s | %s | %s | \n"; _connect(); my $format = "DMY1_YYYY"; #my $sth->prepare( "set dateformat dmy" ); my $sth =$connection->prepare(my $sql = "SELECT Release, dtTargetF +eatureFreeze, dtTargetCCRB, dtTargetReleaseFromDev, dtTargetRTM, Twik +iURL, ReleaseNum, Display FROM ReleaseDates WHERE (Display = 1)"); #$sth->do( "set dateformat dmy" ) || die $sth->$errstr; $sth->execute; my $result = sprintf $fmt, @headers; while (my @rows = $sth->fetchrow_array() ) { $rows[0]="<a class=\"twikilink\" href=\"$rows[5]\">$rows[0]</A>"; $result .= sprintf $fmt, @rows; } $sth->finish; return $result; } 1;
TEST SCRIPT
#! /usr/bin/perl -w # # Small exmaple tool for DB connection # Written by Jazz 2007 use DBI; #Load DBI Module ## Attributes to pass DBI-connect() to disable automatic error checkin +g. my %attr = ( PrintError => 1, RaiseError => 1, ); ## Perform the connect using the Sybase Driver my $datasource = 'DBI:Sybase:192.168.70.56'; my $user = "user"; my $password = "password"; my $dbh = DBI->connect($datasource,$user,$password) or die "Cant connect to Database: $DBI::errstr\n"; # my $dbh = DBI->connect($datasource,$user , $password) # or die "Cant connect to Database: $DBI::errstr\n"; # Prepare a SQL statement for execution my $sth = $dbh->prepare_cached("SELECT * FROM ReleaseDates"); $sth->DATEFORMAT('dmy'); #execute statement in the DB $sth->execute or die "Can't prepare SQL statement : $sth->errstr\n"; #return rows of data while (my @row = $sth->fetchrow_array() ) { print "Row: @row\n"; } warn "Problem in fetchrow_array(): ", $sth->errstr(),"\n" if $sth->err(); ## Disconnect from db $dbh->disconnect or warn "Failed to disconnect: ",$dbh->errstr(),"\n"; exit;
What can i do to get the right format of date out of it? Many thanks again for all your help

Replies are listed 'Best First'.
Re: Using DBI::Sybase having problem with Date format
by derby (Abbot) on May 10, 2007 at 16:45 UTC

    select convert( char(8), gedate(), 1 )
    will convert the current date to mm/dd/yy. Although I would probably use
    select convert( char(10), getdate(), 101 )
    to get mm/dd/yyyy. So just change getdate() with your DATETIME column.

    -derby
      Thanks for the quick reply. I am looking at where I would include this in the script that i attached would I have to run the above as a seperate sql query within that script?

        I don't know which columns are dates (so, I'll assume those prefixed with dt are). For your test script, you would have to change from

        my $sth = $dbh->prepare_cached( "SELECT * FROM ReleaseDates" );
        to
        my $sth = $dbh->prepare_cached( "SELECT convert( char(8), dtXXX, 101 ) FROM ReleaseDates" );
        for the cgi:
        my $sth =$connection->prepare( "SELECT Release, dtTargetFeatureFreeze, dtTargetCCRB, dtTargetReleaseFromDev, dtTargetRTM, TwikiURL, ReleaseNum, Display FROM ReleaseDates WHERE (Display = 1)" );
        to
        my $sth =$connection->prepare( "SELECT Release, convert( char(8), dtTargetFeatureFreeze, 1), convert( char(8), dtTargetCCRB, 1), convert( char(8), dtTargetReleaseFromDev, 1), convert( char(8), dtTargetRTM, 1), TwikiURL, ReleaseNum, Display FROM ReleaseDates WHERE (Display = 1)" );
        -derby

        update: BTW, setting dateformat only affects inputting dates.

Re: Using DBI::Sybase having problem with Date format
by mpeppler (Vicar) on May 11, 2007 at 06:20 UTC
    DBD::Sybase also supports the following:
    $dbh->syb_date_fmt('MDY1_YYYY');
    for this functionality. This works with the Sybase libraries, and I assume that this also works with FreeTDS.

    Michael

      mpeppler ... syb_date_fmt works great with the Sybase libs but for freetds, it appears to be a no-op (and that's the latest version of freetds, the version ubuntu packages does not even have that method).

      -derby
        Thanks for the update. I guess that this isn't one of the "important" functions that have priority to get implemented in FreeTDS...

        Michael

        Your right I could not get this to work. Your convert reply works a dream derby thanks again your a total star!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://614678]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (7)
As of 2021-01-24 23:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?