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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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