?
Solved

VB6 - Query Date Format

Posted on 2005-02-27
9
Medium Priority
?
223 Views
Last Modified: 2013-12-25
while i am trying execute the query,  i found the problem "date out of range". its comes more times.

if i gave the format is "dd-MM-yyyy"  then some times i got the error is the date is more that 12. if i run the same in the different pc, its works fine. i dont want to change the date format in the other pc. how can i solve this problem. all the both machines have SQLServer 2000.

i am using SQL Server 2000.
0
Comment
Question by:vinspire
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 13417945
Try formatting the date in your query.

something like:
format(yourDate,"dd-mm-yyyy")
0
 
LVL 3

Accepted Solution

by:
bc10 earned 200 total points
ID: 13417969
What is your local?  The standard format for SQL is the American date format  i.e MM-dd-yyyy.  However the European date format is dd-MM-yyyy.  If you give a format with a day greater than 12, SQL thinks you are giving a month greater than 12.  When building SQL strings, I always use the long date format dd-MMM-yyyy i.e 13-Feb-2005 and let SQL convert to its own internal format.  This way there is no confusion.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13426126
You need to usea a non-ambiguous date format, such as ODBC (yyyy-mm-dd hh:mi:ss) or ISO8601 (yyyy-mm-dd Thh:mm:ss.mmm)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13426131
Also, please maintain these abandoned questions:
1 10/16/2004 300 Radius Search..  with x,y  Open GIS & GPS
2 11/02/2004 95 MEDS - IE6 Problem  Open Windows Security
0
 

Author Comment

by:vinspire
ID: 13436688
hi
in my local settings i set as General Setting as US format, but the program is running on European Settings in some other m/c. in that case even if i set the format as "dd-MM-yyyy" some queries its working fine and some it wont.

is that possible set the proper settings for the sqlserver.
0
 
LVL 3

Expert Comment

by:bc10
ID: 13436967
When creating the sql query, use the data format dd-MMM-yyyy e.g. Select * from table where Date='14-Jan-2005'.  Changing the local settings on your pc or on the SQL Server will have no effect.  The change must be in the query string.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13439659
Once again, use a non-ambiguous date format and you do not have to worry about regional locale's.
0
 

Author Comment

by:vinspire
ID: 13442522
hi
how to set non-ambiguous date format from Vb?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13442821
Format(YourDate, "yyyy-mm-dd")
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

770 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