Query stops retuning records after 6:00pm - Datetime datatype

  • gjoelson 29755

    Right there with Babe

    Points: 750

    Hi ,  I have a query that uses a where clause to return records today.  however  it seems at 6:00pm it stops returning the records.

    My script is using a left join and both tables use the datetime data type.

    I've tried different approaches but cant seem to find the solution.

    where  [TransactionDate] >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

    I also tried ....

    WHERE [TransactionDate] >= dateadd(day, datediff(day, 0, getdate()), 0) 
    And [TransactionDate] < dateadd(day, 1, dateadd(day, datediff(day, 0, getdate()), 0))

    Seems using a  cast after 6:00pm worked and it did return records but , I couldn't get totals right using this.

    SELECT 
    y.[Date]
    ,y.[Customer]
    ,y.[Product]
    ,sum( t.nettons) Tons

    FROM [SA-Releases] Y

    left Join [LoadoutTransaction] t
    on y.customer = t.companyname
    and y.Product = t.productname
    And t.{some date column} >= y.[Date]
    And t.{some date Column} < dateadd(day, 1, y.[Date])

    WHERE [Date] = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

    --[Date] >= dateadd(day, datediff(day, 0, getdate()), 0)
    --And y.[Date] < dateadd(day, 1, dateadd(day, datediff(day, 0, getdate()), 0))

    GROUP BY y.[Date],y.customer,
    y.product, t.productname
    --, t.nettons

    order by 1

    thanks for any help.

     

     

  • ScottPletcher

    SSC Guru

    Points: 98441

    What is the data type of the TransactionDate column?  If it's in the date/datetime category, then this condition:

    where [TransactionDate] >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

    should pull all rows on or after today at midnight.  You must have some other issue somewhere else.

    Would you provide DDL for (all relevant columns of) the table?

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ZZartin

    SSC-Dedicated

    Points: 30414

    Do you have any examples of records you think should be showing up that aren't?

  • oogibah

    SSC Eights!

    Points: 802

    I'm a noob, but what if you casted them as date so it doesnt include the  time portion?

    where CAST([TransactionDate]  AS DATE)=CAST(GETDATE() AS DATE)

     

     

     

     

     

    • This reply was modified 5 months, 3 weeks ago by  oogibah.
  • ScottPletcher

    SSC Guru

    Points: 98441

    oogibah wrote:

    I'm a noob, but what if you casted them as date so it doesnt include the  time portion?

    where CAST([TransactionDate]  AS DATE)=CAST(GETDATE() AS DATE)

    That seems like a slick solution.  The problem is performance.  CASTing a column to a different type, or using any function against a column, can prevent index seeks, which can be a very severe performance hit.

    SQL Server may now have some built-in techniques to make this specific CAST not an issue, but I'm not 100% sure that's guaranteed, and I'd prefer to avoid every CASTing a column anyway as just poor technique.

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • oogibah

    SSC Eights!

    Points: 802

    ScottPletcher wrote:

    oogibah wrote:

    I'm a noob, but what if you casted them as date so it doesnt include the  time portion?

    where CAST([TransactionDate]  AS DATE)=CAST(GETDATE() AS DATE)

    That seems like a slick solution.  The problem is performance.  CASTing a column to a different type, or using any function against a column, can prevent index seeks, which can be a very severe performance hit.

    SQL Server may now have some built-in techniques to make this specific CAST not an issue, but I'm not 100% sure that's guaranteed, and I'd prefer to avoid every CASTing a column anyway as just poor technique.

     

    I don't know if it is any better but what about using

    WHERE [TransactionDate] >= LEFT(GETDATE(),11)?

    • This reply was modified 5 months, 3 weeks ago by  oogibah.
  • gjoelson 29755

    Right there with Babe

    Points: 750

    So I  attach the Table schema (I think it got blocked)  ,  both date fields are Datetime type.

    I don't have an actual example of a record not showing up because they shoul all be showing up until end of day 00:00:00

    Funny I actually tride to do a CAST ,  but could only get records if I added -7 , this is what I used.

    CAST([TransactionDate]  AS DATE) > DATEADD(DAY, 0, CAST(GETDATE() AS DATE))

     

    Table A.

    /****** Object:  Table [dbo].[SA-Releases]    Script Date: 1/11/2020 12:29:53 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[SA-Releases](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Customer] [varchar](50) NULL,
    [Product] [varchar](max) NULL,
    CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[SA-Releases] ADD CONSTRAINT [DDTStamp] DEFAULT (getdate()) FOR [Date]
    GO

    Table B.

    /****** Object:  Table [dbo].[LoadoutTransaction]    Script Date: 1/14/2020 12:55:55 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[LoadoutTransaction](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Address] [nvarchar](255) NULL,
    [City] [nvarchar](255) NULL,
    [CompanyId] [int] NOT NULL,
    [CompanyName] [nvarchar](50) NULL,
    [Destination] [nvarchar](255) NULL,
    [Gross] [float] NOT NULL,
    [Net] [float] NOT NULL,
    [NetTons] [float] NOT NULL,
    [PoNumber] [nvarchar](255) NULL,
    [Postal] [nvarchar](50) NULL,
    [ProductId] [int] NOT NULL,
    [ProductName] [nvarchar](255) NULL,
    [State] [nvarchar](50) NULL,
    [Tare] [float] NOT NULL,
    [TicketNumber] [nvarchar](50) NOT NULL,
    [TransactionDate] [datetime] NULL,
    [TransactionMode] [nvarchar](10) NULL,
    [TruckNumber] [nvarchar](50) NULL,
    [Void] [bit] NOT NULL,
    [InboundDateTime] [datetime] NULL,
    [SiloNumber] [int] NULL,
    [JobId] [nvarchar](50) NULL,
    CONSTRAINT [PK_dbo.LoadoutTransaction] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[LoadoutTransaction] ADD DEFAULT ((0)) FOR [SiloNumber]
    GO

     

  • ZZartin

    SSC-Dedicated

    Points: 30414

    I don't have an actual example of a record not showing up because they shoul all be showing up until end of day 00:00:00

    Well since the table has an identity on it, you could get the max identity you see in your query(need to take out the aggregate and add the identity), then search for anything with a greater identity after 6 PM 😛

     

     

    • This reply was modified 5 months, 3 weeks ago by  ZZartin.
  • oogibah

    SSC Eights!

    Points: 802

    If you're just looking for todays date you shouldn't need the dateadd(day,0,getdate()) at all you would just need the getdate()..

    I've used the cast that I posted above in quite a few queries I do to look in my audit tables and what not, I've never had issue with it.. my immediate assumption given the information provided is that either the join is bad or you just don't have any data for today's date?

     

    I notice in your join you are doing this as well

    And t.{some date column} >= y.[Date]
    And t.{some date Column} < dateadd(day, 1, y.[Date])

    In my brain, and again I'm a noob so I could be wrong, this seems redundant/incorrect with using date in the where clause?

    • This reply was modified 5 months, 3 weeks ago by  oogibah.
  • ScottPletcher

    SSC Guru

    Points: 98441

    Thanks for the DDL.  Yeah, the standard format of:

    WHERE TransactionDate >= CAST(GETDATE() AS DATE))

    will work just find for finding dates >= today at midnight.

    Funny I actually tride to do a CAST ,  but could only get records if I added -7 , this is what I used.

    Because rows don't exist in the table for datetimes after that.  Maybe somehow the TransactionDate is being left NULL?  That could really should be NOT NULL.

    Much more importantly, for best (even really decent) performance, your clustering key should be ( TransactionDate, Id ) not just ( Id ).

    The PK can still be Id alone, but it should be NONCLUSTERED.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply