Solved

Inserting date

Posted on 2000-04-25
22
187 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
  • 8
  • 7
  • 2
  • +3
22 Comments
 

Expert Comment

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

Author Comment

by:enryo
Comment Utility
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
Comment Utility
Lets see your exact SQL statement.

M
0
 
LVL 12

Expert Comment

by:mark2150
Comment Utility
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
Comment Utility
Cloud_1 changed the proposed answer to a comment
0
 
LVL 6

Expert Comment

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

this would work for date
0
 

Author Comment

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

Expert Comment

by:bhess1
Comment Utility
As they said, can you show your exact SQL Statement?

Also, what DB are you using?
0
 

Author Comment

by:enryo
Comment Utility
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
Comment Utility
"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
Comment Utility
Or...

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

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Expert Comment

by:Cloud_1
Comment Utility
Or...

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

0
 

Author Comment

by:enryo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks,I have tried both but still getting the syntax error.
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now