Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

VB6 - Query Date Format

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
vinspire
Asked:
vinspire
  • 4
  • 2
  • 2
  • +1
1 Solution
 
DhaestCommented:
Try formatting the date in your query.

something like:
format(yourDate,"dd-mm-yyyy")
0
 
bc10Commented:
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
 
Anthony PerkinsCommented:
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
Industry Leaders: 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!

 
Anthony PerkinsCommented:
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
 
vinspireAuthor Commented:
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
 
bc10Commented:
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
 
Anthony PerkinsCommented:
Once again, use a non-ambiguous date format and you do not have to worry about regional locale's.
0
 
vinspireAuthor Commented:
hi
how to set non-ambiguous date format from Vb?
0
 
Anthony PerkinsCommented:
Format(YourDate, "yyyy-mm-dd")
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now