Why does MySQL not convert dates before 2010-01-01?
-
Hi, I have been trying to add some data to a table we get which is sometimes incomplete This is the SQL command that is used to fill in a "shipment_date"
UPDATE IGNORE myTable
SET Shipment_Date = CONCAT(LEFT(Sales_Order_Ship_Date,4), "-",
MID(Sales_Order_Ship_Date, 2, 2), "-",
RIGHT(Sales_Order_Ship_Date, 2) );In this case Shipment_date is an actual DATE data type whereas the Sales_Order_Ship_Date is a VARCHAR(42) data type. It works really well on the condition that the original string data represents a date of at least 2010-01-01. The string data is formatted as follows: YYYYMMDD so 2010-01-01 is 20100101 in the string version. It converts perfectly to 2010-01-01 and the same is true for later dates. Any string representing a date before 2010-01-01 though comes out as 0000-00-00. Does anyone have any ideas as to what is causing this?
-
Hi, I have been trying to add some data to a table we get which is sometimes incomplete This is the SQL command that is used to fill in a "shipment_date"
UPDATE IGNORE myTable
SET Shipment_Date = CONCAT(LEFT(Sales_Order_Ship_Date,4), "-",
MID(Sales_Order_Ship_Date, 2, 2), "-",
RIGHT(Sales_Order_Ship_Date, 2) );In this case Shipment_date is an actual DATE data type whereas the Sales_Order_Ship_Date is a VARCHAR(42) data type. It works really well on the condition that the original string data represents a date of at least 2010-01-01. The string data is formatted as follows: YYYYMMDD so 2010-01-01 is 20100101 in the string version. It converts perfectly to 2010-01-01 and the same is true for later dates. Any string representing a date before 2010-01-01 though comes out as 0000-00-00. Does anyone have any ideas as to what is causing this?
Well,
MID('20091231',2,2)is '00' MID is used like:MID( string, start_position, length)where start position is one based, not zero based. So try usingMID(Sales_Order_Ship_Date,5,2)instead.Wrong is evil and must be defeated. - Jeff Ello Never stop dreaming - Freddie Kruger
-
Well,
MID('20091231',2,2)is '00' MID is used like:MID( string, start_position, length)where start position is one based, not zero based. So try usingMID(Sales_Order_Ship_Date,5,2)instead.Wrong is evil and must be defeated. - Jeff Ello Never stop dreaming - Freddie Kruger
Your answer was right on the mark, thanks a lot for that. :) I first tried it on MySQL Tryit Editor v1.0[^] and it worked perfectly there. Then I corrected my colleague's original code as you described and sure enough it now works exactly as expected. Why it ended up producing 0000-00-00 is still a mystery but once the dissection of the string is done properly it now accepts any date
-
Hi, I have been trying to add some data to a table we get which is sometimes incomplete This is the SQL command that is used to fill in a "shipment_date"
UPDATE IGNORE myTable
SET Shipment_Date = CONCAT(LEFT(Sales_Order_Ship_Date,4), "-",
MID(Sales_Order_Ship_Date, 2, 2), "-",
RIGHT(Sales_Order_Ship_Date, 2) );In this case Shipment_date is an actual DATE data type whereas the Sales_Order_Ship_Date is a VARCHAR(42) data type. It works really well on the condition that the original string data represents a date of at least 2010-01-01. The string data is formatted as follows: YYYYMMDD so 2010-01-01 is 20100101 in the string version. It converts perfectly to 2010-01-01 and the same is true for later dates. Any string representing a date before 2010-01-01 though comes out as 0000-00-00. Does anyone have any ideas as to what is causing this?
Just use the CONVERT function,
SELECT CONVERT(Sales_Order_Ship_Date , Date);
Social Media - A platform that makes it easier for the crazies to find each other. Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.