Avatar of Andrew Angell
Andrew AngellFlag for United States of America

asked on 

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!

ASP

Avatar of undefined
Last Comment
fritz_the_blank
Avatar of B_Dorsey
B_Dorsey

try

INSERT INTO Invoices (CreationDate) VALUES (#2/20/2007#)
Avatar of Andrew Angell
Andrew Angell
Flag of United States of America image

ASKER

that gives me a parse error.
SQL likes your insert to look like this:

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
Try this

INSERT INTO Invoices (CreationDate) VALUES ('2007-02-20')

What is your database Access-SQL server
ASKER CERTIFIED SOLUTION
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
ASP
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo