Subject Re: D2109 - INSERT INTO for a literal date doesn't seem to recognise From Mervyn Bick <invalid@invalid.invalid> Date Mon, 3 Mar 2025 11:13:06 +0200 Newsgroups dbase.getting-started On 2025/03/03 07:05, Ken Mayer wrote: > On 3/2/2025 2:06 PM, BRUCE HEATH wrote: >> Hi, >> >> I'm playing with dBase 2109 and noticed that INSERT INTO seems a bit >> flaky when loading  DATE information. >> >> I have a simple rountine that uses INSERT INTO (date_field) VALUES >> (date_string) and I'm trying to load a date in DD/MM/YY format. Both >> the Windows 10 system and the Dbase ini file have the date format set >> to DD/MM/YY >> >> e.g. >> INSERT INTO (fd) VALUES ("27/12/59") >> This prompts a :Database Engine Error : Type mismatch in expression" >> error. >> >> if I change it to american format e.g. >> >> INSERT INTO (fd) VALUES ("12/27/59")   //so MM/DD/YY format >> >> it goes through without a fault, but I'm wondering why the command fails. >> >> It seems as though the command doesn't recognise either the system >> format or the dBase SET DATE format. >> >> When I retrieve the data, however, it's in the "right" format (DD/MM/ >> YY) even though the load in requires MM/DD/YY. > > Dates usually must be entered in a year/month/day format for SQL, and > the INSERT INTO command is local SQL via the BDE, or the current date > format for your database and dBASE. How is the software supposed to know > what parts of > > What part of either of the three parts of the date is supposed to be the > day of the month, the month, and the year? It's a good idea to use four- > digit years, as well. (If you used for your data 25 for the year, is > that 1925, 2025 ...?) > > Try using:  1958/12/27 The BDE uses the separator to determine what each of the three parts of a literal date represents.  If the separator is / (forward slash) then the three parts are month, day, year i.e American format. If the separator is . (point) then the three parts are day, month year i.e German format.  Forward slash and point are the only options for the separator.  In other words, only American or German format is allowed for literal dates in localSQL. SQL requires literal dates to be delimited using single quotes. localSQL will accept either single or double quotes.  Square brackets, which dBASE will normally accept as string delimiters, are not accepted as delimiters for literal dates. Leading zeros for day and month are optional. Provided FOURDIGITYEAR and YEARBIASED are both set TRUE in the BDE a two digit year is acceptable.  "3/1/25" will give the same result as "03/01/2025" Mervyn.