DateTime comparison - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DateTime comparison

Author

Topic

olily
Starting Member

37 Posts

Posted - 2002-10-08 : 21:53:45

I have the below code written in VB6 and using SQL7. When I run my program in my local computer I have correct result. But when I run the same program in terminal server, it returns incorrect result. One thing I notice that is always the second row is incorrect regardless the statement return how many rows. The second row quantity will always less 6 than the one I run from local computer. For example:
From local computer(correct result):
Assembly1, 10
Assembly2, 20
Assembly3, 30

From Terminal Server:
Assembly1, 10
Assembly2, 14 (less 6)
Assembly3, 30

dim strsql as string

strsql = "SELECT ASSEMBLY, COUNT(DISTINCT SERIALNO) AS RECORD FROM " & strTable & " WHERE DATESAVE BETWEEN '" & dtpShiftStart & "' AND '" & dtpShiftEnd & "' GROUP BY ASSEMBLY ORDER BY ASSEMBLY"

dtpShiftStart and dtpShiftEnd are in this format: mm/dd/yyyy hh:mm:ss

Any idea where goes wrong with my sql statement?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 22:47:05

Try using the format function in vb to cast the Shift start and Shift end dates, specify the date format and see if that works for you.

I've I had problems with dates before because date casting uses the system settings to convert dates

olily
Starting Member

37 Posts

Posted - 2002-10-08 : 23:25:36

I tried by using the sql below:

strSql = "SELECT ASSEMBLY, COUNT(DISTINCT SERIALNO) AS RECORD FROM " & strTable & " WHERE DATESAVE BETWEEN '" & _
Format(dtpShiftStart, "mm/dd/yyyy hh:mm:ss") & "' AND '" & _
Format(dtpShiftEnd, "mm/dd/yyyy hh:mm:ss") & _
"' GROUP BY ASSEMBLY ORDER BY ASSEMBLY"

The result still the same. Always the first 6 records are not selected. Really reaching deadend now....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-08 : 23:33:55

Try using the ISO date format (YYYYMMDD, no spaces, no dashes). It sounds like the two machines have different regional settings...I know that might not make sense, but if they are set differently, try changing them to match and see if it affects the results.

BTW, do you have data with date values that could be ambiguous? Meaning, they could be valid dates in both MM/DD/YY and DD/MM/YY formats?

olily
Starting Member

37 Posts

Posted - 2002-10-09 : 01:18:56

I couldn't get how to change the datetime format to ISO using VB and in sql query. Can you please provide me the coding? Below are some of the records from database:

Assembly SerialNo DateTime
ASSEMBLY1 B02419JR 2002-10-08 07:03:00.000
ASSEMBLY1 B02419JV 2002-10-08 07:03:00.000
ASSEMBLY1 B02419NS 2002-10-08 07:04:00.000
ASSEMBLY1 B02419HY 2002-10-08 07:06:00.000
ASSEMBLY1 B02419HR 2002-10-08 07:07:00.000
ASSEMBLY1 B02419HS 2002-10-08 07:07:00.000
ASSEMBLY1 B02419J2 2002-10-08 07:08:00.000
ASSEMBLY1 B02419HT 2002-10-08 07:08:00.000

If I run the sql statement:
strSql = "SELECT ASSEMBLY, COUNT(DISTINCT SERIALNO) AS RECORD FROM " & strTable & " WHERE DATESAVE BETWEEN '" & _
Format(dtpShiftStart, "mm/dd/yyyy hh:mm:ss") & "' AND '" & _
Format(dtpShiftEnd, "mm/dd/yyyy hh:mm:ss") & _
"' GROUP BY ASSEMBLY ORDER BY ASSEMBLY"

Result in local machine:
ASSEMBLY1 6

Result in terminal server:
ASSEMBLY1 2

Thanks.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-09 : 03:15:41

You can convert dates to ISO format using the CONVERT function like this: CONVERT(varchar, datefield, 112)

More info concerning conversion of datetime fields can be found in BOL.

/Andraax

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-09 : 09:19:05

To elaborate on Andraax's suggestion, you might want to consider creating a stored procedure that accepts two parameters, one for the start date and one for the end date. That way you can pass them in any format you wish, and have the stored procedure handle the conversion. It also makes it much easier to maintain and modify your code without having to rewrite every application that might use this query.

olily
Starting Member

37 Posts

Posted - 2002-10-16 : 03:01:55

I noticed that only the first 6 records are ignore even it is valid in my sql statement. Sample record:
ASSEMBLY1 B02419JR 2002-10-08 07:03:00.000
ASSEMBLY1 B02419JV 2002-10-08 07:03:00.000
ASSEMBLY1 B02419NS 2002-10-08 07:04:00.000
ASSEMBLY1 B02419HY 2002-10-08 07:06:00.000
ASSEMBLY1 B02419HR 2002-10-08 07:07:00.000
ASSEMBLY1 B02419HS 2002-10-08 07:07:00.000
ASSEMBLY1 B02419J2 2002-10-08 07:08:00.000
ASSEMBLY1 B02419HT 2002-10-08 07:08:00.000

The first 6 records are when the time is between 07:01:00.000 to 07:07:00.000. For other datetime, the result returned are correct.
I have tried the methods suggested but result still the same. Any idea?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources