Solved

Date format in SQL query string

Posted on 2014-12-14
10
247 Views
Last Modified: 2014-12-27
I have a slight problem with date formating.

I want to lookup a vb,net date in an SQL table and i keep getting errors, the first part of teh code (SET) is fine, the issue is the StartDate and CycleStart which are both dates.  

SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE StartDate =  & CycleStart &  ")

Any help most welcome.
0
Comment
Question by:SweetingA
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40498640
Pass the date as a string, formatted as yyyymmdd (a language-neutral literal).

SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE StartDate = '"  & format(CycleStart,"yyyymmdd") &  "'")
0
 

Author Comment

by:SweetingA
ID: 40498665
Does not error now but still yields no result

Datetime shown Vb is #1/26/2014 3:02:50 PM# and teh date i need to compare to in SQL is 2014-09-13 15:04:50.000.

Thanks for the help so far.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40498765
Ah, you're comparing datetimes rather than dates. The two datetimes you want to compare are not identical. Was that deliberate or a typo?

If you want to get a datetime within a one day range...
SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE StartDate between '"  & format(CycleStart,"yyyymmdd") &  "' AND  Dateadd(dd,1,'" &  format(CycleStart,"yyyymmdd") & "')")

if you want the exact comparison, I think you want this
SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE StartDate = '"  & format(CycleStart,"yyyymmdd hh:mm:ss") &  ":000'")
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 11

Expert Comment

by:LordWabbit
ID: 40498766
I agree with SimonAdept, in that using an ISO standard date of yyyy-mm-dd should resolve all date issues, the fact that you are not getting any results probably means that the date is not being stored as a datatype of datetime in the database, but is probably being stored as a varchar.  

Also never use inline SQL, EVER!  It is one of the most common forms of attack on the internet (SQL Injection)

Even if it is an internal intranet site it's a bad habit and should never be used, use parameters instead and if you absolutely have to continue using inline SQL at least sanitize your inputs.

If the datetime is stored as varchar casting it to a datetime could resolve your problems
SQL.RunQuery("UPDATE temptbl_Stops SET DowntimeCode = '" & dc & "' WHERE CAST(StartDate AS DATETIME) = '"  & format(CycleStart,"yyyymmdd") &  "'")

Open in new window


This will not work if all of the StartDate's are not valid datetime values.
0
 

Author Comment

by:SweetingA
ID: 40498873
Datestime is stored as datetime not varchar
0
 

Author Comment

by:SweetingA
ID: 40498884
I have tried several different formats including adding - between year/m/d but makes no difference, no errors but does nothing
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 40499045
Well to test perhaps run the statement directly against the database.  Is there a rollback in place?  Are you seeing a "records affected count" more than once, which is signs of a trigger changing your update?
0
 

Author Comment

by:SweetingA
ID: 40499141
Looks like you can only reformat a datetime to a string and not to a date time, once this is done no direct comparison can be made.

An sql count statement works only if i type in the dtae, if i type in the variable it returns nothing.

I am very much a novice so you will have to be patient.
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40499262
A sql select count(*) query is a good way to test... as you have done. Yes, the format() function returns a string, but if it's recognisable as a date, SQL Server will treat it as one, though not always the date you expect.
If you're getting no results from your count(*), try the > operator instead of the = operator and return a list of matching values rather than just a count. This might illustrate the reason for the unexpected result.
Do you have access to the design of the table you're querying? Can you check which datatype the column is?
Note that there are six possible date/time datatypes in MSSQL 2008:

Even yyyy-mm-dd is not foolproof for the datetime and smalldatetime datatypes. See this blog post among others. On my own system it gets interpreted as yyyy-dd-mm, unless I use the full ISO spec:
YYYY-MM-DDThh:nn[:ss[:mmm]]
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40499663
Does not error now but still yields no result

Datetime shown Vb is #1/26/2014 3:02:50 PM# and teh date i need to compare to in SQL is 2014-09-13 15:04:50.000.

#1/26/2014 3:02:50 PM# can never equal 2014-09-13 15:04:50.000 no matter what formats you use; they just are not equal.

1/26/2014 is: 2014-01-26 ; and  2014-01-26 <> 2014-09-13 ; plus the times don't match either.

Please provide some sample data and the expected result. Make sure datetime values contain as much precision as possible.

Remember the precision of datetime is something like 3.5 milliseconds (from memory) so getting 2 datetime values  that are equal can be like trying to split an arrow that hit the bullseye.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
vb.net and creating a class 5 20
Create a datatable in vb.net dynamically 1 22
Iteration Help (Asp.net VB) 5 24
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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