January 14, 2020 at 5:42 pm
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.
January 14, 2020 at 5:52 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2020 at 6:06 pm
Do you have any examples of records you think should be showing up that aren't?
January 14, 2020 at 6:40 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2020 at 6:47 pm
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)?
January 14, 2020 at 6:59 pm
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
January 14, 2020 at 7:01 pm
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 😛
January 14, 2020 at 7:35 pm
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?
January 14, 2020 at 8:47 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply