Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

SQL Insert Statement for date

by phimtau123 (Novice)
on Jul 16, 2007 at 22:36 UTC ( #626938=perlquestion: print w/ replies, xml ) Need Help??
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; } }

Comment on SQL Insert Statement for date
Download Code
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");
    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
Re: SQL Insert Statement for date
by spatterson (Monk) 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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (7)
As of 2014-08-02 10:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (55 votes), past polls