January 21, 2013 at 10:20 am
16 posts into what should have been a 1-2 post answer if ddl and sample data were provided...
Here is my last shot in the dark.
SELECT
CASE LEFT(cast(Customer.STARTDATE as varchar(20)), 2)
WHEN '98' THEN substring(cast(Customer.STARTDATE as varchar(20)), 3, 2)
WHEN '99' THEN substring(cast(Customer.STARTDATE as varchar(20)), 3, 2)
END AS LossMo
FROM
dbo.Customer
If this doesn't help you may want to turn up the lights for the rest of us. The light switch is in ddl, sample data and desired output as outlined in the link your signature. Feel free to read the same article at the first link in my signature. If that doesn't help try reading the article here. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2013 at 12:53 pm
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.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2013 at 1:19 pm
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'.
January 21, 2013 at 6:26 pm
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;
January 22, 2013 at 6:34 am
The following works :
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) = '98' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
WHEN LEFT(Customer.StartDate, 2) = '99' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
END AS LossMo
FROM Customer
The StartDate column is numeric (7,2).
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 22, 2013 at 6:42 am
Welsh Corgi (1/22/2013)
The following works :
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) = '98' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
WHEN LEFT(Customer.StartDate, 2) = '99' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
END AS LossMo
FROM Customer
The StartDate column is numeric (7,2).
Thanks.
Well, I'm glad you got what ever it was you were trying to do working. Too bad you couldn't be bothered with providing us with the information we asked for, including a descriptiong of the problem, sample data, and expected results.
Perhaps next time you will be willing to provide all of that.
January 22, 2013 at 6:47 am
Welsh Corgi (1/22/2013)
The following works :
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) = '98' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
WHEN LEFT(Customer.StartDate, 2) = '99' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
END AS LossMo
FROM Customer
The StartDate column is numeric (7,2).
Thanks.
That one was a curve ball - numeric data won't have a leading zero:
Welsh Corgi (1/19/2013)
...The Date is stored in an AS400 DB2 format.
The 1st character 1 or 0 - Century, where 1 > the year 2000 (21st century and 0 is 1999 (20th centrury)...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2013 at 7:09 am
ChrisM@Work (1/22/2013)
Welsh Corgi (1/22/2013)
The following works :
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) = '98' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
WHEN LEFT(Customer.StartDate, 2) = '99' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
END AS LossMo
FROM Customer
The StartDate column is numeric (7,2).
Thanks.
That one was a curve ball - numeric data won't have a leading zero:
Welsh Corgi (1/19/2013)
...The Date is stored in an AS400 DB2 format.
The 1st character 1 or 0 - Century, where 1 > the year 2000 (21st century and 0 is 1999 (20th centrury)...
Even better, the data he did provide early won't fit in a numeric(7,2) data type:
declare @TestData table (
AS400Dates numeric(7,2)
);
insert into @TestData
VALUES
(991231),
(991015),
(970704),
(1080518),
(1080707),
(1080515),
(1080731),
(1080815),
(1080822),
(1080911),
(1080916),
(1080925),
(1080926),
(1080927),
(1081023);
SELECT
AS400Dates,
19000000 + AS400Dates DateAsInt2,
CAST(CAST(19000000 + AS400Dates AS VARCHAR) AS DATE) DateAsDate,
month(CAST(CAST(19000000 + AS400Dates AS VARCHAR) AS DATE)) as TheMonth
FROM
@TestData;
Error:
Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
(0 row(s) affected)
March 25, 2013 at 12:58 pm
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) = '98' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
WHEN LEFT(Customer.StartDate, 2) = '99' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
END AS LossMo
FROM Customer
From a purely logical point of view since both conditions lead to the same logical branch it is cleaner to writes
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) in ('98','99') THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
END AS LossMo
FROM Customer
What do you do when values are not in this small set?
----------------------------------------------------
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply