November 26, 2021 at 4:11 pm
I have a table [UPSBatchInvNo] with column [BatchDate] formatted as 112621.
SELECT CONVERT(varchar,CAST(LEFT(BatchDate, 2) + '/' + SUBSTRING(BatchDate, 3,2) + '/' + '20' + SUBSTRING(BatchDate, 5,2) as Date),101) AS BatchDate gives me expected result: 11/26/2021. Great.
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) gives me expected results: 11/26/2021
So why does this query net no results?
SELECT WhsID,
CONVERT(varchar,CAST(LEFT(BatchDate, 2) + '/' + SUBSTRING(BatchDate, 3,2) + '/' + '20' + SUBSTRING(BatchDate, 5,2) as Date),101) AS BatchDate,
Count(InvNo) AS OrderCount
FROM [UPSBatchInvNo]
GROUP By WhsID, BatchDate
HAVING BatchDate=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY WhsID
Thanks!
November 26, 2021 at 5:49 pm
The date could be converted without using string functions. Also, the WHERE clause might be a more appropriate place to filter the rows. Maybe something this
declare @UPSBatchInvNot table(WhsID int,
BatchDate int);
insert @UPSBatchInvNot(WhsID, BatchDate) values
(1, 112621);
select u.whsID, v.calc_dt
from @UPSBatchInvNot u
cross apply (values (datefromparts(u.BatchDate%100+2000,
u.BatchDate/10000,
u.BatchDate/100%100))) v(calc_dt)
where v.calc_dt=cast(getdate() as date);
whsID calc_dt
1 2021-11-26
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 26, 2021 at 5:51 pm
Are you using Access to write your queries? Shouldn't that HAVING clause be a WHERE clause?
Why are you storing dates as text?
November 26, 2021 at 6:03 pm
Because BatchDate is a string formatted as MMDDYY and you are trying to compare that to another string formatted as MM/DD/YYYY. What you are expecting is that BatchDate in the HAVING clause is referencing your calculated value - it is not going to use that.
If you want to only get those results for today - it should be done in the WHERE clause and you don't need to convert the BatchDate column. Instead, use this:
WHERE BatchDate = replace(convert(char(8), getdate(), 1), '/', '')
Convert with format 1 returns MM/DD/YY - replace removes the dashes resulting in MMDDYY and for today that would be 112621.
For your select, I am assuming you want an actual date - so add slashes or dashes and cast/convert:
Declare @stringDate char(8) = '112621';
Select cast(stuff(stuff(@stringDate, 5, 0, '/'), 3, 0, '/') As date);
Caveat: this assumes a US English language and may not work appropriately if the values in BatchDate are not in MMDDYY format - or if the system is set to a different language. That is why it would be much better to have a non-ambiguous format - either YYYYMMDD or YYYY-MM-DD. Better yet, the column should be defined as a date data type and when populated (assuming this is from a file) that process should reformat to an appropriate value that can be implicitly converted.
Another option would be a computed persisted column - using the above calculation. Then - you don't need any conversions in code as you can query the computed column using normal date checking.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2021 at 9:35 pm
Thank you all! Great help!
December 2, 2021 at 10:28 pm
>> I have a table [UPSBatchInvNo] with column [ batch_date] formatted as 112621.<<
First of all, we prefer that people post DDL and not a narrative. We have no idea what data type this batch_date column is. But it sure as hell is not a DATE data type like it should be . What you've done is something the COBOL programmer would do; building a string!
What is even worse is that this string you are building is not in ISO 8601 format, but some local dialect. The only format allowed in ANSI/ISO standard SQL is yyyy-mm-dd. Please notice the use of dashes and not slashes, the length of fields and their order.. We spent some time on the committee, making sure that a date could not be mistaken for any other string. You also don't seem to understand the order in which the clauses of a select statement are executed. The having clause would be executed after the group by. The order by clause technically computes your answer from a table into a local cursor, at some expense. Do the display formatting in the presentation layer, not in the query layer of your tiered architecture.
What I think you meant to post is:
CREATE TABLE UPSBatchInv
(..,
batch_date DATE NOT NULL,
..);
SELECT warehouse_id, COUNT (inv_nbr) AS order_cnt
FROM UPSBatchInv
WHERE batch_date = CAST (CURRENT_TIMESTAMP AS DATE)
GROUP BY warehouse_id;
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply