none
Query returns incorrect results

    Question

  • I have a database named Mirror1_Server, which is located in Windows Server 2008 and managed by MS SQL Server 2008. I used a static IP to connect: select * from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham] and I got incorrect results for three executions:

    00:00:31 1480 rows

    00:00:40 1407 rows

    00:00:38 1460 rows

    I don't know why. Please help me. Thanks a lot!

    Thursday, September 12, 2013 4:23 PM

Answers

  • Datetime attributes store date and time. In the first query you are passing '2013-09-13 00:00:00.000' and thus you will not get rows with values in Ngay_Ct = '2013-09-13 13:12:00.000'.

    The second query will give you both rows with '2013-09-13 00:00:00.000' and '2013-09-13 13:12:00.000'.

    If you want queries to return the same results write the first one like this:

    Option 1:

    select
    *
    from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham]
    where Ngay_Ct >= convert(datetime, '20130913 00:00:00.000', 112)
    	and Ngay_Ct < convert(datetime, '20130914 00:00:00.000', 112)

    Option 2:

    select
    *
    from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham]
    where convert(date, Ngay_Ct) = convert(date, '20130913', 112)

    Also, I see you are using four part nameing, so I presume you are selecting from linked server. It is better to use OPENQUERY for this kind of operation as it will be passed through to SQL where data is stored and executed there.

    OPENQUERY

    Using Pass-Through Queries As Tables


    Regards, Dean Savović

    Friday, September 13, 2013 7:00 AM
  • where Ngay_Ct='9/13/2013'

    where Year(Ngay_Ct)=2013 and Month(Ngay_Ct)=9 and Day(Ngay_Ct)=13

    These two queries are only equivalent under the following circumstances:

    1. Ngay_Ct's time portion is always 00:00:00 (See Dean's response)
    2. The connection's DATEFORMAT is set to mdy

    If you don't explicitly set the date format, then the default settings will be used, and they may easily differ between drivers or applications.

    If you want to use a safe date literal, you should use the format YYYYMMDD, for example: WHERE Ngay_Ct = '20130913'

    If your datetime columns stores dates with time portions, then you should query a half over date range, for example: WHERE Ngay_Ct >= '20130913' AND Ngay_Ct < '20130914'


    Gert-Jan

    Friday, September 13, 2013 5:16 PM

All replies

  • Your view may have different rows for each execution. How did you define incorrect results? Can you show your view definition to see whether it has timestamp related clause?  Thanks.
    Thursday, September 12, 2013 4:33 PM
    Moderator
  • Here is my view, could you tell me where is problem? Thanks!

    SELECT     Ct0.Id, Ct0.ParentId, Ct0.Stt, Ct0.BuiltinOrder, Ct0.Ngay_Ct, Ct0.Ma_Gd, Ct0.Ma_Dt, Ct0.Ma_Kho, Ct0.Ma_Vt, 
                          (CASE WHEN Ct0.Ten_Vt <> '' THEN Ct0.Ten_Vt ELSE DmVt.Ten_Vt END) AS Ten_Vt, Ct0.Dvt, Ct0.So_Luong, Ct0.Gia_Nt, Ct0.Gia, Ct0.Gia_Nt2, Ct0.Gia2, Ct0.Tien_Nt, 
                          Ct0.Tien, Ct0.Tien_Nt2, Ct0.Tien2, Ct0.Tien_Nt9, Ct0.Tien9, Ct0.Ma_Ct
    FROM         ABC.B70_NhuaTp_KD.dbo.B30CtVt AS Ct0 INNER JOIN
                          ABC.B70_NhuaTp_KD.dbo.B30Ct AS Ct ON Ct0.Stt = Ct.Stt AND Ct0.Ngay_Ct = Ct.Ngay_Ct LEFT OUTER JOIN
                          ABC.B70_NhuaTp_KD.dbo.B00DmCt AS DmCt ON Ct0.Ma_Ct = DmCt.Ma_Ct LEFT OUTER JOIN
                          ABC.B70_NhuaTp_KD.dbo.B20DmVt AS DmVt ON Ct0.Ma_Vt = DmVt.Ma_Vt
    WHERE     (Ct.Ma_Ct = 'HD') AND (Ct.IsActive <> 0)

    Thursday, September 12, 2013 5:14 PM
  • There are no obvious problems with this view.

    Are any of the "tables" in there actually views? If so, what are their declarations?

    Also, consider running sp_refreshview on the view.

    To elaborate: usually different results are caused by different connection settings, that may affect NULL settings and other ANSI settings, which can change the semantics.


    Gert-Jan

    Thursday, September 12, 2013 5:47 PM
  • Data is changing between executions: ct.Ma_Ct or Ct.IsActive. Or data in join predicate columns.

    Regards, Dean Savović

    Thursday, September 12, 2013 5:52 PM
  • There is no change between executions and there is no view in defenition. I don't know why.
    Thursday, September 12, 2013 6:10 PM
  • In definition of view there is'nt any view but there are 3 timestamp columns in 3 tables which make this view. Is it problem?
    Thursday, September 12, 2013 6:17 PM
  • Please post DDL. This code made no sense to me. 

    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    Thursday, September 12, 2013 7:45 PM
  • I recognize that:

    select *

    from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham]

    where Ngay_Ct='9/13/2013': It always returns correct results.

    , but:

    select *

    from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham]

    where Year(Ngay_Ct)=2013 and Month(Ngay_Ct)=9 and Day(Ngay_Ct)=13: It returns defference results.

    Type Ngay_Ct is datetime.

    Please explain why???


    --The Anh--

    Friday, September 13, 2013 1:12 AM
  • Datetime attributes store date and time. In the first query you are passing '2013-09-13 00:00:00.000' and thus you will not get rows with values in Ngay_Ct = '2013-09-13 13:12:00.000'.

    The second query will give you both rows with '2013-09-13 00:00:00.000' and '2013-09-13 13:12:00.000'.

    If you want queries to return the same results write the first one like this:

    Option 1:

    select
    *
    from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham]
    where Ngay_Ct >= convert(datetime, '20130913 00:00:00.000', 112)
    	and Ngay_Ct < convert(datetime, '20130914 00:00:00.000', 112)

    Option 2:

    select
    *
    from [118.6.122.50,1430].[Mirror1_Server].[dbo].[ViewSanPham]
    where convert(date, Ngay_Ct) = convert(date, '20130913', 112)

    Also, I see you are using four part nameing, so I presume you are selecting from linked server. It is better to use OPENQUERY for this kind of operation as it will be passed through to SQL where data is stored and executed there.

    OPENQUERY

    Using Pass-Through Queries As Tables


    Regards, Dean Savović

    Friday, September 13, 2013 7:00 AM
  • where Ngay_Ct='9/13/2013'

    where Year(Ngay_Ct)=2013 and Month(Ngay_Ct)=9 and Day(Ngay_Ct)=13

    These two queries are only equivalent under the following circumstances:

    1. Ngay_Ct's time portion is always 00:00:00 (See Dean's response)
    2. The connection's DATEFORMAT is set to mdy

    If you don't explicitly set the date format, then the default settings will be used, and they may easily differ between drivers or applications.

    If you want to use a safe date literal, you should use the format YYYYMMDD, for example: WHERE Ngay_Ct = '20130913'

    If your datetime columns stores dates with time portions, then you should query a half over date range, for example: WHERE Ngay_Ct >= '20130913' AND Ngay_Ct < '20130914'


    Gert-Jan

    Friday, September 13, 2013 5:16 PM