SQL Insert Statement for date

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

How can i change my insert statement so i will insert a date into the database? I've set the datatype in the database as dat/time with shortdate as the format.

sub Scan_File {
    my ($variables) = @_;
    
    my $dbh = DBI->connect('DBI:ODBC:mwalarm')
      or die "Couldn't connect to database: " . DBI->errstr;
    my $sql = "INSERT INTO Alarm_Info(EquipmentID, Chamber, LotID, Waf
+er_Flow, Recipe, Wafer_Slot, Step_Value, 
        AlarmType, Sensor, Signal_Level, Alarm_Upper_Limit, Alarm_Lowe
+r_Limit, Code, Date_Submited, Time_Submited, 
        FilesName, UserCode)
     VALUES     ('$eqpid', '$chamber', '$lotid', '$wafer_flow', '$reci
+pe', '$wafer_slot', '$step_value', '$alarmtype', 
         '$sensor', '$signal_level', '$alarm_upper_limit', '$alarm_low
+er_limit', '$code', '$date_submited', 
         '$time_submited', '$filenames', '$usercode')";
        #sendToLog ($sql) ;
    my $sth = $dbh->prepare($sql)
      or print ERROR "Couldn't prepare statement: " . $dbh->errstr;

    if ($alarmtype =~ /Clear/i)    {
        unlink "$scan_dir\\$filename" or print ERROR "can't unlink Cle
+ar $scan_dir\\$filename $!\n";
        
    }else {
        $sth->execute() or die "cant execute" . $dbh->errstr;

        $sth->finish;
    }

}
[download]

Comment on SQL Insert Statement for date Download Code

Replies are listed 'Best First'.
Re: SQL Insert Statement for date by aquarium (Curate) on Jul 16, 2007 at 23:24 UTC
dates/times are stored internally in database fields in compressed format, and SQL select statements do the translation for you, even displaying in locally set display variation. No such shortcut for insertion/update though. you'll need to find out the name of the specific ToDate or ToTime function of your specific database engine. each database engine (oracle, mysql, mssql, etc.) stores and handles dates/times differently, and have their own function to handle such. an example insert for a ficticious database engine would be something like `insert into this_table (ToDate("17-07-2007");` [download] of course the actual format of the date you pass in is database engine specific, using particular delimiters and honoring (or not) your locale setting for date display. the hardest line to type correctly is: stty erase ^H	[reply] [d/l]
Re: SQL Insert Statement for date by spatterson (Pilgrim) on Jul 17, 2007 at 15:31 UTC
If you use a date format your database understands and set the field type to timestamp then your date insert should work. Databases differ in understanding dates, but should all be able to cope with ISO-SQL format: YYYY-MM-DD HH:MM:SS.sss (down to milliseconds). Some databases accept DEFAULT CURRENT_TIMESTAMP as a date column constraint so the database would insert the current time when the row is inserted just another cpan module author	[reply]