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;
[download]

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;
[download]

What can i do to get the right format of date out of it? Many thanks again for all your help

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.