Subject Re: Insert cmd will not work with date()
From Randy Waldman <randwald@comcast.net>
Date Thu, 26 Feb 2015 23:12:57 -0500
Newsgroups dbase.getting-started

Mervyn - you are incredible!  You don't know how many hours I hacked on this very thing.  thanks a million.  When I win the lottery, half is yours!
Randy

Mervyn Bick Wrote:

> On Thu, 26 Feb 2015 04:21:35 +0200, Randy Waldman <randwald@comcast.net>  
 > wrote:
 >
> > Hi.  Trying to use the INSERT command for a date() (+ other fields).  
> > dBase9.5 gives various errors when attempting to insert a date.
 > > Try this:
 > > 1.  Create test.dbf with (1) field (date)
 > > 2.  Insert INTO test VALUES (date())
 > >
 > > thanks, Randy
 >
> Firstly, it's not a good idea to name a field "date" as this is one of the  
 > reserved word in SQL. (See "reserved words" in the OLH)  dBASE does allow  
 > this but when you get a bit deeper into SQL you'll find that, while  
 > permitted, fields named using reserved words need special handling and  
 > this can become a nuisance.  It is better to avoid using reserved words as  
 > field names from day one.
 >
>
> INSERT INTO ... is a SQL command.  In SQL, dates must be in American  
 > 'mm/dd/yyyy' or German 'dd.mm.yyyy' format no matter what the date formant  
 > of the computer is set to.
 >
>
> An alternative is to use a parameter (ie a variable prefixed with a : ) in  
 > the SQL command.  In this case one needs to pass a date type as the  
 > parameter.
 >
> Mark and copy the the code below.  In the command panel enter   modi comm  
 > test4randy  then paste the code into the sourcecode editor that will  
 > open.  Save and run.
 >
> In the example I've used date = new date() to create the value for the  
 > parameter.  This ensures that the code will run properly no matter what  
 > your computer's data format is set to.  You can, of course, use curly  
 > brackets to create a date but you then need to use the appropriate  
 > format.  As "date" in this case is a dBASE variable and not SQL teh  
 > restriction on reserved words does not apply.
 >
>
> Mervyn.
 >
>
> ******* Start of example code **********
 > if file('test4randy.dbf')
 >     drop table test4randy
 > endif
 >
> if not file('test4randy.dbf')
 >     create table test4randy  (ddate date)
 > endif
 >
> insert into test4randy  (ddate) values ('02/24/2015')
 >
>
> //using a parameter
 >
> date = new date(2015,1,28) //months are zero based in the date class so 1  
 > is Feb.
 > insert into test4randy (ddate) values (:date)
 >
> use test4randy
 > list
 > use
 > ******* End of example code **********