Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Sing Chung Hii
  Where is Sing Chung Hii?
 Klang
 Malaysia
 Sing Chung Hii
 To: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 Tags
Subject: RE: data type mismatch when insert date
Thread ID: 209374 Message ID: 209382 # Views: 3 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Sunday, December 28, 2008 8:23:21 AM         
   


> > Need expert assistance.
> > Using this SQL statement ends up "Data type mismatch":
> >
> > INSERT INTO invoice (invoice_no,buyer_order_no,currency,customerid,date,delivery_date,pinvno,payment_term,remark) values("10000001","PO10001","USD",1001,27/12/2008,04/01/2009,"10000001","30 days after B/L","1st shipment 30 days after payment")
> >
> > Through elimination of fields and values, I confirmed that the problem lies in the date fields (the date and the deliverydate fields). I've tried putting double-quotes, single quotes and no quotes around the date values, the results were the same - data type mismatch. When putting "{" and "}" around the date values, it resulted in "Ambiguous date/datetime constant. Use the format: {^yyyy-mm-dd[,][hh[:mm[:ss]][a|p]]}"
> >
> > How do I get around this?
>
> Glenn showed you one, another is to use this format: {^yyyy-mm-dd}
>
>
INSERT INTO invoice (invoice_no,buyer_order_no,currency,customerid,date, ;
> delivery_date,pinvno,payment_term,remark) values("10000001","PO10001","USD", ;
> 1001,{^2008-12-27},{^2009-01-04}, "10000001","30 days after B/L", ;
> "1st shipment 30 days after payment")
> 

>
> Jun Tangunan
> http://weblogs.foxite.com/sandstorm36/

Also, how do I insert empty date into the field, for example, the deliverydate are most often leftout by user as it is not compulsory, when retrieving this value and insert into another table, the empty value should also be copied over. However date() can't covert an empty date (function argument,value or type is invalid), how do I resolve that?

How do I set a validation rule on a date field to make it mandatory?

ENTIRE THREAD

data type mismatch when insert date Posted by Sing Chung Hii @ 12/28/2008 3:18:20 AM
RE: data type mismatch when insert date Posted by Glenn Villar @ 12/28/2008 3:31:09 AM
RE: data type mismatch when insert date Posted by Jun Tangunan @ 12/28/2008 3:38:53 AM
RE: data type mismatch when insert date Posted by Sing Chung Hii @ 12/28/2008 7:54:29 AM
RE: data type mismatch when insert date Posted by tushar @ 12/28/2008 10:04:32 AM
RE: data type mismatch when insert date Posted by Sing Chung Hii @ 12/28/2008 8:23:21 AM
RE: data type mismatch when insert date Posted by Stefan Wuebbe @ 12/28/2008 10:22:49 AM
RE: data type mismatch when insert date Posted by vinod kabdal @ 12/29/2008 1:55:14 PM
RE: data type mismatch when insert date Posted by tushar @ 12/29/2008 2:54:11 PM
RE: data type mismatch when insert date Posted by venkataramesh cheruvu @ 12/29/2008 4:31:04 PM