Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bosshoff
Starting Member
9 Posts |
 Posted - 2004-10-14 : 18:14:44
|
| Dear Sirs, I have a question concerning the way SQL Server treates datetime values. I am designing a time clock program that is networked to multiple buildings (hence the applicability of SQL Server). I have the employee-side of the program running well, but have been encountering problems with the management portion. I am using an Access front end, and trying to do all of the dog work through stored procedures. Here's an example of where I'm running into trouble: I total up punches and place them into a table, to be displayed later in a report. These semi-totaled entries contain a [Time In], [Time Out], and other miscellaneous information. The [Time In] and [Time Out] fields are of a datetime type, and so is the data that I am filling these fields with. When I am in an Access form and wish to change a punch, SQL gets executed to find the details of that particular punch via the following query:SELECT [Type Description] FROM times JOIN types ON times.Type = types.Type WHERE ID='" & employeeNum & "' AND (Timestamp='" & punchIn & "' OR Timestamp='" & punchOut & "') ORDER BY Timestamp ASC"where employeeNum, punchIn, and punchOut are VBA variables. Now, when I execute this query, I sometimes get an error that says that the record could not be found. I do not see how this is possible, since the punchIn and punchOut variables contain strings that identically match the entries in the raw punches table. I am not trying to look up just dates or anything: I have the time strings stated out to the second, and direct comparison isn't working. I have a couple workarounds currently going, one where I define a range of +/- one second for each punch, and another where I use LIKE instead of '='. However, I would rather learn exactly why this is happening so I can, at least, understand the discrepency and, at most, find a way to correct it. I am sorry for my long winded explanation, but so often do I see people post a one sentence question only to be asked for more information. Please let me know if you do need more information, and I thank you for your help. |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-14 : 19:54:24
|
Hi bosshoffYou are most likely encountering 2 different problems:1. Firtsly, there is an implicit type cast occuring in the WHERE clause of your query. You are placing punchIn and punchOut into a string. That string gets sent to SQL server. SQL server, being the nice guy that it is, then takes a guess at what time it thinks that string equates to. You don't really have much control over whether that conversion happens correctly or not, unless you cast your values explicitly. The string may look the same when you compare them, but Access VBA does not always represent values exactly as they are stored (even in debug mode!). You can do the type casting either at the SQL Server end, or the Access end, but probably best to do it on both ends to make absolutely sure.2. Secondly, and not unrelated to the typecast, some of the times are probably not matching due to a difference in accuracy of your datetime values (especially since you're not casting them explicitly). You may be comparing one time which is different only in milliseconds, especially if you are using Timestamp rather than datetime.To solve your problem I would do the following:AND (convert(Timestamp, varchar(20), 120) = '" & Format(punchIn, "yyyy-mm-dd hh:mm:ss") & "' OR (convert(Timestamp, varchar(20), 120) = '" & Format(punchOut, "yyyy-mm-dd hh:mm:ss") & "') ORDER BY Timestamp ASC"That may need some tweaking, but it should significantly improve the robustness of your code.To explain, I am using convert on the SQL Server side to ensure that it will convert the timestamp to a known format - to the nearest second. I am also using Format on the VBA side, to ensure that those values are also converted to exactly the same format. Now you are comparing apples with apples.However, even with this I'm not sure you will always get a 100 percent match due to possible accuracy differences, and so you may still need to revert to a +/- range depending on various issues.Please let me know how you go.CheersPS - We're not all Sirs  --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
bosshoff
Starting Member
9 Posts |
Posted - 2004-10-16 : 23:21:52
|
| Hello, I don't think I have ever gotten such a salient reply to a post in my career as a programmer! Thanks a lot rrb. Although I haven't gotten around to trying this yet, I can see that it will probably solve my problems. Now the only question is, do I want to make my code more cryptic while potentially sacrificing effectiveness. Basically, all the queries I'm executing will deal with one employee at a time. Since I am only opening the range by +/- one second, I can be reasonably sure that no one employee can make two punches that close together in time. So, the way I am currently executing the query is thusly:SELECT [Type Description] FROM times JOIN types ON times.Type = types.Type " _ & "WHERE ID='" & employeeNum & "' AND (Timestamp BETWEEN '" & DateAdd("s", -1, punchIn) _ & "' AND '" & DateAdd("s", 1, punchIn) & "') OR " _ & "(Timestamp BETWEEN '" & DateAdd("s", -1, punchOut) & "' AND '" _ & DateAdd("s", 1, punchOut) & "') ORDER BY Timestamp ASC"Well, now that I look at it, I guess that code isn't too intuitive, is it?! I guess I'll just have to test your method, rrb, and if it is as fail-safe as I think it will be, I will adopt it. Thanks a bunch for taking the time to answer my post.P.S.: Would passing the datetime values directly to a stored procedure or function eliminate these workarounds completely? If so, how would I avoid passing them as strings? Thanks again. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-17 : 19:53:50
|
| Hey bossGlad to hear you appreciated my post. No, you really can't get around the problem by passing the values to a stored procedure (although that may well make your code more "intuitive").Unless you're using some method I've never heard of, (ie not ADO or ODBC) then effectively your dates get passed as strings no matter what you do. However, separating the code out into a stored procedure (rather than one long concatenated string) is definitely also a good idea for clarity, manageability, reusability and performance.I'd still be interested in hearing how you finally go.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
bosshoff
Starting Member
9 Posts |
Posted - 2004-10-18 : 12:22:52
|
| Hello, Well, I gave it a try on a SELECT query, trying a bunch of different records to see if they were all successfully retrieved. To my dismay, they weren't all found, though the occurences were small. I find it odd that I can't either use a datatype or conversion that simply drops the milliseconds extention. I do not need it, but I do need seconds, which rules out the smalldatetime data type. I guess I'll just have to keep doing it the same, messy way I have been. Thanks a bunch for the help anyway rrb. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-10-18 : 13:31:33
|
| I would add a ID collumn and call it from your Access form that way.I do this all the time, the ID field does not need to be visable to do this. This will save a lot of problems.JimUsers <> Logic |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-18 : 19:09:17
|
| Hi Bosshofyou said "Well, I gave it a try " - you may not have time, but I'd really like to know what exactly you gave a try. Can you post your query? As for your comment about dropping the milliseconds, I'm not really sure what you mean...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
bosshoff
Starting Member
9 Posts |
Posted - 2004-10-18 : 22:33:19
|
| Hey rrb, Here's what I tried:"SELECT [Type Description] FROM times JOIN types ON times.Type = types.Type " _ & "WHERE ID='" & employeeNum & "' AND (CONVERT(varchar(20),Timestamp,120)='" _ & punchIn & "' OR CONVERT(varchar(20),Timestamp,120)='" & punchOut & "') " _ & "ORDER BY Timestamp ASC" I think the problem is that, when the punch is entered, milliseconds is included (via the GetDate SQL function), so there is a 50/50 chance that the milliseconds will be > 500. Therefore, when I make the comparison, it must round up on that instance, therefore making the test fail. So, what I meant by dropping the milliseconds is exactly that: if I could truncate the milliseconds from the datetime I am comparing to before making the comparison (and before rounding) I would solve the problem. Jiml, I think I see what you're saying; however, I have a listbox that I do not want to add unrelevant information to, and these queries work of the information presented in this listbox, so I think that route isn't feasable. I can see how you would come up with such a conclusion given my lack of description of the problem, so I do thank you for the effort.I believe the simplest and therefore, in my eyes, best solution is simply to ensure that the milliseconds extension of every punch is always zero. There is only one place where the punch actually gets inserted, so this would be much easier than modifying a dozen or more select statements with some workaround. Thanks again guys for helping me to consider my options. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-18 : 23:01:45
|
| i'm a lazy reader, is your problem on passing datetime to a query? enclose the datetime in #, like:"SELECT [Type Description] FROM times JOIN types ON times.Type = types.Type " _& "WHERE [ID]='" & employeeNum & "' AND (Timestamp='#" _& punchIn & "#' OR Timestamp='#" & punchOut & "#') " _& "ORDER BY Timestamp ASC"or use datediff, just curious though, why do you need the exact time?--------------------keeping it simple... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-18 : 23:04:29
|
| hi bosshofYou're still not formatting the punchIn and punchOut values. Also, to truncate the ms, you can doselect convert(nvarchar(19), Timestamp, 121)I don't think the vBA times are accurate to milliseconds...so you should be OK--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
bosshoff
Starting Member
9 Posts |
Posted - 2004-10-19 : 21:52:20
|
| rrb,Thanks a bunch man. I could've sworn I put in the vba-side formatting, but I guess I didn't. After making a simple query to try the following CONVERT statementsCONVERT(nvarchar(19), Timestamp, 121)CONVERT(varchar(20),Timestamp,120)I found that the top one serves my needs perfectly. I will use this for now, until I fix the INSERT statement to have a .000 milliseconds extension. Thanks a lot for your help, rrb, and for giving me a better understanding of how datetime works.------------------------------------------------SELECT [Hot Women] FROM anywhere WHERE [Marriage Status]='Single' AND [STDs]='0' ORDER BY [Yearly Income] DESC:) |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-19 : 21:59:59
|
| no worries - hope it helps--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
Query Direct Comparison ( '=' operator)