[RESOLVED] Cdate Data Type Mismatch

[Always_Confused]

VB6 SP6

I am try to convert a date string to date and insert it into a table (modDate Date). I am getting a data mis-match error but in debug, it appears that the data has been converted.

Code:
Dim ModDate1 As Date
ModDate1 = CDate(Format(flDrawings.ModDate, "MM/DD/YYYY"))

"VALUES ('" & IIf(IsNull(Name$), "a", RTrim(Name$)) & "','" & IIf(IsNull(Revision$), Null, RTrim(Revision$)) & "','" & IIf(IsNull(Descr$), Null, RTrim(Descr$)) & "'," & ModDate1 & ")"

Immediate Results
INSERT INTO engfiles(drwName,Rev,Descr,ModDt)VALUES ('WIDGETII','a','',7/12/2007)

[westconn1]

why would you format the date data then convert it to a date as the formatting will be lost and the date will be as per you systems date format
which part of the code gives the error?

[si_the_geek]

As westconn1 correctly said, the Date data type does not store formatting - only the value (and even tho it seems to be displayed as text, it is certainly not stored that way).

Depending on what format you are using (and the system date format) this is likely to just change the date. 7/12/2007 could be July 12th or Dec 7th, and after running that code you cannot be sure which will be stored in your variable.

If you want to store the formatted value to a variable, store it to a String variable.


Next up, to use a date in an SQL statement you need to put some sort of delimiter around the value (otherwise you are basically using the mathmatical calculation (7/12)/2007 , which is 2.9e-4 !).

To see which delimiter you need, read the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[GaryMazzone]

What is the backe end DB. If Access then the date value (7/12/2007) needs to be surrounded by pould signs (#7/12/2007#). If SQL Server then surround with single qoutes ('7/12/2007'). Oracle needs the To_Date Function.

MS Access

VALUES ('" & IIf(IsNull(Name$), "a", RTrim(Name$)) & "','" & IIf(IsNull(Revision$), Null, RTrim(Revision$)) & "','" & IIf(IsNull(Descr$), Null, RTrim(Descr$)) & "',#" & ModDate1 & "#)"

SQL Server

VALUES ('" & IIf(IsNull(Name$), "a", RTrim(Name$)) & "','" & IIf(IsNull(Revision$), Null, RTrim(Revision$)) & "','" & IIf(IsNull(Descr$), Null, RTrim(Descr$)) & "','" & ModDate1 & "')"

Oracle

VALUES ('" & IIf(IsNull(Name$), "a", RTrim(Name$)) & "','" & IIf(IsNull(Revision$), Null, RTrim(Revision$)) & "','" & IIf(IsNull(Descr$), Null, RTrim(Descr$)) & ",To_Date('" & ModDate1 & "','MM/DD/YYYY'))"

[Always_Confused]

The data source for the date is from a fixed length file (ModDate As String * 10) which has been entered in as e.g. 8-22-2007.

The source DB is a VFP9 free table (modDate Date)
Code:
Dim ModDate1 as Date
ModDate1 = flDrawings.ModDate

"VALUES ('" & IIf(IsNull(Name$), "a", RTrim(Name$)) & "','" & IIf(IsNull(Revision$), Null, RTrim(Revision$)) & "','" & IIf(IsNull(Descr$), Null, RTrim(Descr$)) & "'," & ModDate1 & ")"

This is what I get on debug.print, it appears to be correct but errors.
INSERT INTO engfiles(drwName,Rev,Descr,ModDt)VALUES ('WIDGETII','a','',7/12/2007)

[Always_Confused]

Also tried Gary's post with single quote

INSERT INTO engfiles(drwName,Rev,Descr,ModDt)VALUES ('WIDGETII','a','','7/12/2007')

[si_the_geek]

For VFP, I would guess that date delimiter would be the same as Access (using #).

it appears to be correct but errors.
INSERT INTO engfiles(drwName,Rev,Descr,ModDt)VALUES ('WIDGETII','a','',7/12/2007)

As I explained before, that is definitely wrong - you need delimiters of some sort (otherwise you will be storing tiny numbers, rather than dates).

You also need to format date values before/whilst putting them into an SQL statement (either format them and store them to a string, or use the format function while appending them to the SQL).

[Al42]

You also need to format date values before/whilst putting them into an SQL statement (either format them and store them to a string, or use the format function while appending them to the SQL).

Or store them as dates (so that things like BETWEEN work) and format them after retrieval.

[Always_Confused]

Like this? Could you give me an example?

Dim ModDate1$

ModDate1$ = flDrawings.ModDate
'" & Format(ModDate1$, "MM/DD/YYYY") & "')

I tried

#" & Format(ModDate1$, "MM/DD/YYYY") & "#)

but got syntax error.

[si_the_geek]

You could use this:

Dim ModDate1 as String
ModDate1 = Format(flDrawings.ModDate, "MM/DD/YYYY")

... & "',#" & ModDate1 & "#)"

or this:

Dim ModDate1 as Date
ModDate1 = flDrawings.ModDate

... & "',#" & Format(ModDate1, "MM/DD/YYYY") & "#)"

or this:

... & "',#" & Format(flDrawings.ModDate, "MM/DD/YYYY") & "#)"

(all of these are assuming that the delimiter is # , tho you may need to use ' instead).

Or store them as dates (so that things like BETWEEN work) and format them after retrieval.

Unless I read it incorrectly, the table values are Dates ("The source DB is a VFP9 free table (modDate Date) ").

Inside the SQL statement (which is a string), you cannot have actual dates - only strings that represent dates. To work correctly, they need to be formatted in a way that will be interpreted by the DBMS as you intended (which means formatting them as mm/dd/yyyy, or better yyyy-mm-dd).

If a command object was being used, using a Date value with a parameter object is correct - as it will basically do the formatting for you.

[Always_Confused]

The source data is a fixed length file, the target is a VFP9 free table. (sorry I had source in there twice as the VFP table should have been target)

The date has been entered into the file in the format of "08-22-2007"

Public Type drwFile
Name            As String * 200         'drawing name for the loop
Revision        As String * 32          'revision value for this drawing
CrDate          As String * 10          'date the drawing was created
ModDate         As String * 10          'date the drawing was last changed/overwritten

[si_the_geek]

Is the modDate field (in the database) a Date data type, or is it a String/Char?

[Always_Confused]

It is a Date type. The source is string/char

[si_the_geek]

In that case you need to use one of the formats that I mentioned, but you already have that from the source, with something that may or may not be issue (it has - rather than / ).

Depending on how VFP wants to receive dates, one of these will hopefully work for you:
... & "',#" & flDrawings.ModDate & "#)"
... & "','" & flDrawings.ModDate & "')"
... & "',#" & Replace(flDrawings.ModDate,"-","/") & "#)"
... & "','" & Replace(flDrawings.ModDate,"-","/") & "')"

If not, you should check the documentation to find out what it wants.

[Always_Confused]

Got it! The delimeter is Brackets

... "', {" & Replace(LstRefDat$, "-", "/") & "},

[si_the_geek]

Excellent, thanks for sharing the solution.

So that I can add it to the FAQ properly, can you just confirm that by VFP9 you mean MS Visual Foxpro 9?

[Always_Confused]

Yes VFP9 is Visual Foxpro v9. This should also be valid back to at least v6 as well.

For those looking for a client side or server side database far more robust than Access, look into Foxpro! Microsoft is still supporting Foxpro despite of what you hear and has just released Sedna to enhance the technologies to version 9. Version 9 is to be supported to 2015.

http://www.microsoft.com/downloads/d...ng=en#Overview

The DB stucture will be around for a long time and now has varchar fields. I have found it perfect for my situation as an intermediate step before taking the application to full blown SQL. And it requires no DB engine files to run in the background on individual installations as does MSDE and others.

Another plus, the vfpoledb driver is free to download via Microsoft and does not require you to purchase the application to develop the free tables or DB.

[si_the_geek]

Thanks for the confirmation - I'll add it to the FAQ now.

While I see that VFP could be useful (and I'm sure many people will use it), there is a new version of SQL Server (CE) which does basically the same thing - with the added benefits that the upgrade path to a 'bigger' version of SQL Server is much simpler, and that it presumably runs on more devices (such as PDAs).

SQL Server CE is also a free download, and there is also has a free management tool for designing tables etc - there are links to both in the SQL Server 2005 thread at the top of the Database forum.