date is field name not data type and its data type is DATETIME
DATE(`MyDateColumn`) AS `Date`,
COUNT(`MyDateColumn`) AS `Record count`
WHERE `MyDateColumn` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`MyDateColumn`)
ORDER BY `MyDateColumn`;
Basically it appears that your database itself has invalid data.
How could that happen? Something pushed it in there before it was validated.
Or perhaps there is some problem with timezones and/or an actual bug in the database itself.
You might be able to fix a timezone problem but otherwise you would need to update the row to put a correct value in there. If it was me I would expect that there is other invalid data in that table and perhaps other tables with timestamps also. You would need to uniquely identify each row then craft an update statement to force that column to a correct (manually determined) value.
I got this off SO, wrt "Code: 1292 SQL State: 22007":
"Change the system timezone to the one that does not use DST ..."
Who knows why, right? When everything you always wanted to know about anything was everywhere. Exactly as it is, I mean.
i have a problem while executing updates using the update script inside the variable, it gives the error "String or binary data would be truncated";
I realized that if reduce the number of fields to update, it no longer gives the error!
But I didn't want to divide the execution of the update into 2 variables!!! Can you help?
After thinking about this a bit more than required;
Member 11533892 wrote:
But I didn't want to divide the execution of the update into 2 variables!!!
You mean you don't want to execute two update-statements for the same record. Two variables cost nothing nowadays, so that doesn't make any sense. Two updates must be atomic, or you get concurrency issues. Could be simply solved by using a transaction.
You may find the error to be the design of the table, not the update-statement. I'm going to guess a lot of those fields can be "NULL" or revert to some preset default value.
..but, let me give you a solution from a VB6 programmer view; you can rename all columns into numbers. So if column one is named [Name], you replace that with . You can keep a list in Excel to look up the column names. That way, the update-statement is shorted a lot, and a huge lots of them would fit in a VARCHAR(MAX) command.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables.
The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal
The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance.
I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order).
I tried This:
SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance
JOIN Orders ON OrderPymts.OrderId = Orders.OrderId
WHERE (Balance >= 0.01)
ORDERBY Orders.OrderId, PaymentDate
To just get the last payment but it still returns all the payments.
Example Result of My query:
OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance| 1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 |
1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 | 1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 |
Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this.
Thanks for your help.
Assuming Microsoft SQL Server, ROW_NUMBER is your friend.
WITH ctePayments As
ROW_NUMBER() OVER (PARTITIONBY OrderId ORDERBY PaymentDate DESC) As RN
Orders As O
INNERJOIN ctePayments As P
ON P.OrderId = O.OrderId And P.RN = 1WHERE
P.Balance >= 0.01ORDERBY
Alternatively, you could use a correlated sub-query:
Orders As O
SELECTTOP1 Terms, PaymentDate, PrevBal, Payment, Balance
WHERE OrderPymts.OrderId = O.OrderId
ORDERBY PaymentDate DESC
) As P
P.Balance >= 0.01ORDERBY
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.
Never underestimate the power of human stupidity -
I'm old. I know stuff - JSOP
--script to modify for multiple database
DECLARE @name VARCHAR(100) -- database name
Declare @url as Varchar(max) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory e.g. 'https://development.blob.core.windows.net/backups'
SET @url = 'https://development.blob.core.windows.net/backups'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR READ_ONLY FOR
WHERE name NOT IN ('master','model','msdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
SET @fileName = @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO URL = @URL