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: 209380 # Views: 4 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Sunday, December 28, 2008 7:54:29 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/


Thanks. I found the {^2008-12-16} way. Then how to convert in one step from {16/12/2008} to {^2008-12-16} or date(2008,12,16)? This is because in real life environment, we won't be hardcoding the date.
I got this ddate=DATE(YEAR(DATE()),MONTH(DATE()),DAY(DATE()))), breaking the date() up then reconsituting, is there a shorter way?

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