Problems with date in SQL INSERT...???
I'm having some troubles trying to insert a date into a FileMaker database via SQL. The following is something I'm trying:
INSERT INTO Invoices (CreationDate) VALUES (2/20/2007)
I don't get any parsing errors with this and the data inserts, however, it's doing the math and inserting 2 divided by 20 divided by 2007. Winds up with a crazy decimal like .0002343023403030.
If I try and put single quotes around the date value FM returns a "query failed" error, and nothing more.
Any information on how I can get the SQL query to treat 2/20/2007 as a date value instead of doing the math would be greatly appreciated. Thanks!
INSERT INTO Invoices (CreationDate) VALUES (2/20/2007)
I don't get any parsing errors with this and the data inserts, however, it's doing the math and inserting 2 divided by 20 divided by 2007. Winds up with a crazy decimal like .0002343023403030.
If I try and put single quotes around the date value FM returns a "query failed" error, and nothing more.
Any information on how I can get the SQL query to treat 2/20/2007 as a date value instead of doing the math would be greatly appreciated. Thanks!
Last Comment
ASKER
that gives me a parse error.
SQL likes your insert to look like this:
INSERT INTO Invoices (CreationDate) VALUES ('2007/02/20')
INSERT INTO Invoices (CreationDate) VALUES ('2007/02/20')
if that doesn't work for you, FileMaker has a native date function:
Date(month;day;year)
So you could do :
INSERT INTO Invoices (CreationDate) VALUES (Date(2;20;2007))
FtB
Date(month;day;year)
So you could do :
INSERT INTO Invoices (CreationDate) VALUES (Date(2;20;2007))
FtB
Try this
INSERT INTO Invoices (CreationDate) VALUES ('2007-02-20')
What is your database Access-SQL server
INSERT INTO Invoices (CreationDate) VALUES ('2007-02-20')
What is your database Access-SQL server
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASP
Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.
82K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
INSERT INTO Invoices (CreationDate) VALUES (#2/20/2007#)