Lynn Pettis (1/21/2013)
Welsh Corgi (1/21/2013)
I had to use a CONVERT with the SUBSTRING Function as opposed to the CAST.This works.
SELECT
CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2)))
= '98' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2))
WHEN LEFT(CAST(Customer.STARTDATE, 2) AS VARCHAR(2)) = '99' THEN
SUBSTRING(CONVFERT(VARCHAR(20),(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LosstMo
FROM Customer
Thanks.
Really? When I post the following into SSMS and click parse I get an error:
SELECT
CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2)))
= '98' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2))
WHEN LEFT(CAST(Customer.STARTDATE, 2) AS VARCHAR(2)) = '99' THEN
SUBSTRING(CONVFERT(VARCHAR(20),(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LosstMo
FROM Customer
Error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Still waiting for you to provide more information about what you are attempting to accomplish and the actual format for the AS400 dates.
Based on the erronous code above, I am guessing you are trying to extract the month from the date, is this correct?
Based on the code I provided, this is how to accomplish that:
declare @TestData table (
AS400Dates VARCHAR(10)
);
insert into @TestData
VALUES
('0991231'),
('0991015'),
('0970704'),
('1080518'),
('1080707'),
('1080515'),
('1080731'),
('1080815'),
('1080822'),
('1080911'),
('1080916'),
('1080925'),
('1080926'),
('1080927'),
('1081023');
SELECT
AS400Dates,
CAST(AS400Dates AS INT) DateAsInt,
19000000 + CAST(AS400Dates AS INT) DateAsInt2,
CAST(CAST(19000000 + CAST(AS400Dates AS INT) AS VARCHAR) AS DATE) DateAsDate,
month(CAST(CAST(19000000 + CAST(AS400Dates AS INT) AS VARCHAR) AS DATE)) as TheMonth
FROM
@TestData;