Link to home
Start Free TrialLog in
Avatar of SweetingA
SweetingA

asked on

Date format in SQL query string

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.
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

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") &  "'")
Avatar of SweetingA
SweetingA

ASKER

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.
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'")
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.
Datestime is stored as datetime not varchar
I have tried several different formats including adding - between year/m/d but makes no difference, no errors but does nothing
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.