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 **********
|
|