Solved

Inserting date

Posted on 2000-04-25
22
194 Views
Last Modified: 2010-05-02
How can i insert a variable to a date field?.
I get a data type mismatch error.
0
Comment
Question by:enryo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +3
22 Comments
 

Expert Comment

by:Cloud_1
ID: 2749049
Use the format #mm/dd/yyyy# including the '#' symbols (and allowing for your local date format)
0
 

Author Comment

by:enryo
ID: 2749080
I have done that but i get a syntax error.I'm trying to add the data with a SQL instruction "INSERT INTO (field1,fieldDate..) VALUES (value1,value2..)" ,in this case value2 is the one that i'm having trouble with.
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2749088
Lets see your exact SQL statement.

M
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 12

Expert Comment

by:mark2150
ID: 2749098
Also,

Cloud_1, welcome to E-E, however a point of netequette. We don't post *answers* unless we're absolutely certain that our answer is *perfect*. The questioner can accept any comment as an answer to award the point and keeping the question open insures that more eyeballs will see it giving the questioner maximum input.

M
0
 

Expert Comment

by:Cloud_1
ID: 2749180
Cloud_1 changed the proposed answer to a comment
0
 
LVL 6

Expert Comment

by:Marine
ID: 2749231
"INSERT INTO (field1,fieldDate..) VALUES (value1,#" & value2 & "#)"

this would work for date
0
 

Author Comment

by:enryo
ID: 2749335
I appreciate all your comments ,but I am still getting a syntax error.
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 2749341
As they said, can you show your exact SQL Statement?

Also, what DB are you using?
0
 

Author Comment

by:enryo
ID: 2749383
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

0
 

Expert Comment

by:Cloud_1
ID: 2749390
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#02/12/2000# );".

The date is in string format... not numeric.


0
 

Expert Comment

by:Cloud_1
ID: 2749395
Or...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 2 & "/" & 12 & "/" & 2000 & "# );".

0
 

Expert Comment

by:Cloud_1
ID: 2749400
Or...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 2 & "/" & 12 & "/" & 2000 & "# );".

0
 

Author Comment

by:enryo
ID: 2749419
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

0
 

Author Comment

by:enryo
ID: 2749441
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

0
 

Expert Comment

by:Cloud_1
ID: 2749457
You're still not formatting the date as a string... try either...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#02/12/2000# );"

....or...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 2 & "/" & 12 & "/" & 2000 & "# );"

8-)

0
 

Author Comment

by:enryo
ID: 2749465
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

0
 

Author Comment

by:enryo
ID: 2749466
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

0
 

Author Comment

by:enryo
ID: 2749482
Thanks,I have tried both but still getting the syntax error.
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 50 total points
ID: 2749528
The problem is that, with your query as formatted below:

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".

What actually is seen by the SQL Parser is:

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#8.333333e05# );".

This is because the section of code:

02 / 12 / 2000

Is evaluated as a mathematical expression.

What you need to do is make that a string of text:

"02/12/2000"

So the full code line would be (as you have it):

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & "02/12/ 2000" & "# );".

No spaces, date inside quotes.

It is possible that the DBase format does not recognize the pound sign for dates.  In this case, you need to check the date identifier for xBase.  A quick review of some info at msdn.microsoft.com indicates that Foxpro, at least, uses { and } to delimit dates.  Try:

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',{" & "02/12/ 2000" & "} );".
0
 

Expert Comment

by:Cloud_1
ID: 2749536
How is the date field store... is it a a date or as another type, possibly?

Do the strings Smith, etc... require 'double' speechmarks (") around them?

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
(""""Smith"""",""""Charles"""",""""18684"""",#02/12/2000#);"

Perhaps place single speech marks around the date...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684','#02/12/2000#');"

Perhaps a combination of both...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
(""""Smith"""",""""Charles"""",""""18684"""",""""#02/12/2000#"""");"
0
 
LVL 8

Expert Comment

by:stefanx
ID: 2749577
The # delimiting of dates is only applicable to Microsoft Access. I can't speak for DBF, but for SQL Server it is a "'" (single quote). Also, instead of trying to adjust to your system settings for date, just specify the date in an unambiguous manner, i.e specify it as dd-mmm-yyyy hh:mm:ss.

In short then,

INSERT INTO SRMM (LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684','12-Jan-2000')



0
 
LVL 8

Expert Comment

by:stefanx
ID: 2749583
The # delimiting of dates is only applicable to Microsoft Access. I can't speak for DBF, but for SQL Server it is a "'" (single quote). Also, instead of trying to adjust to your system settings for date, just specify the date in an unambiguous manner, i.e specify it as dd-mmm-yyyy hh:mm:ss.

In short then,

INSERT INTO SRMM (LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684','12-Jan-2000')



0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month8 days, 7 hours left to enroll

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question