Frustrated with date query...

[PLCBeetle]

Hello all. I need your help please.

I have a MSSql2000 table with a column named last_update with the datetime format.

I am trying to query only the date portion of the data to verify that an entry for a specific day does not exist before inserting a new record. I am not having much luck or finding what I need online.
Here is the code I am trying to use in Access to return the MSSql2000 data:

date_one = [Forms]![frm_oven]![date_one] 'date time pick box on Access form
date1 = Format(date_one, "yyyy-mm-dd") 'date format same as MSSql2000 column

SQLa = "SELECT Temperature FROM Ovens where last_update = '" & date1 & "'"
Set rs = cn.Execute(SQLa, , adCmdText)

If rs.eof = true then 'insert new oven data
Else: MsgBox"Existing data present.": Exit Sub
End If

Thank you for any and all help.

 

[SkipVought]

Hi,

Your form "date" returns TEXT, I'd wager. BTW, it's hardly ever a good idea to have a user enter an UNEDITED date. You often see a control for year, month & day, that will not allow an improper date to be selected.

Well back to your question. TEXT cannot be used in the Format() function. Only a DATE value, which happens to be a pure number in MS Access, that is DISPLAYED in some sort of Date/Time structure.

 

[Olaf Doschke]

Well, as far as I understood you, your last_update column isn't a date, but datetime. Your format of date1 is just the date portion. The comparison last_update = '2014-10-05' will never work, as a string with just a date is implictly converted to the datetime with 0 am as time portion.

last_update musst be between that date 0am and one day later, or make it datediff(hour,date1,last_update) between 0 and 23 (notice: SELECT DATEDIFF(hour, '2014-10-05 00:00:00.0000000','2014-10-05 23:59:59.9999999') is 23).

Bye, Olaf.

 

[gmmastros]

Try this:

SQLa = "SELECT Temperature FROM Ovens where last_update >= '" & date1 & "' and " last_update < '" & DateAdd("d:, 1, date1) & "'"

Basically this query says, "Give me the temperature from Ovens where the datetime >= [user selected date] and datetime < [user selected date + 1 day]"

When you structure your query this way, you will always get the correct result because there are no rounding issues to consider. Additionally, if you have an index on last_update, this query will be able to use the index to drastically improve performance.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PLCBeetle]

I have been trying to format the column data last_update in the sql statement to yyyy-mm-dd and then search for this date.

Won't this work or am I "barking up the wrong tree"?

Thanks

 

[gmmastros]

You can format the data in the column to match your front end, bu tit is the wrong approach because performance won't be as good. Basically, you can format your input (a single value) or you can format all of the data in the table. Guess which is faster.

Have you tried the query I suggested?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PLCBeetle]

Ok, I understand now. Thanks. I am working with your suggested query right now.

 

[gmmastros]

Please let me know if it works, doesn't work, or works and you have questions, etc... I'm curious to know if I was helpful at all.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

>I have been trying to format the column data last_update in the sql statement to yyyy-mm-dd

George has addressed that already, but the main thing is, SQL Server 2000 only has datetime values, which include a time portion. If you only compare to a date, that is converted to the internal binary format and expanded with 0am midnight as the time portion. Only if that matches you get a match. Therefore to catch any datetime value within a day you have to compare with the interval or have to compute a diff and compare that to the range for a day.

Besides any display of values, not only dates, also integers and any other data types, are stored in a format differing from the display. No matter if datetimes are formatted with - or/ as separator, month or day first, the binary storage is the same. And no matter how SQL Server displays dates you can use the format 'yyyymmdd' as locale unspecific format.

In the case of 32 bit integers, the decimal range is about -2 billion to +2 billion, it's stored as 4 bytes but you'd never write something like WHERE intcolum = §4Ö/ though any 4 bytes could be written with 4 ascii characters, it would not only be hard if one of the chars would be backspace or DEL or TAB, it's not human readable and you wouldn't like to code this way, would you? We have been at times you needed punch cards, and programming has evolved, but the internal storage is still in groups of bits storing only 0 or 1, binary formats don't store decimal values nor any other system like the time relying on multiples of 12 or 24, and 60. You alsways have to remember ayour human readable or writabel input is converted to internal formats, even if you don't know them.

Bye, Olaf.