January 19, 2013 at 9:24 am
I'm haveing trouble with a simple CAST to VARCHAR Statement.
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
CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMo
FROM Customer
Any help would be greatly apreciated.
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 19, 2013 at 9:46 am
I forgot to mention a very important point.
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)
The value 1080518 would translate to 2008-05-18.
1080518
1080707
1080515
1080731
1080815
1080822
1080911
1080916
1080925
1080926
1080927
1081023
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 19, 2013 at 11:35 am
Are the date representations integers or characters? How is a date in 1999 or earlier represented?
Also, it would be nice if you presented your informatioin the way you advocate it in your signature block. Please modify the following to properly represent the problem, and please include some dates from prior to 2000-01-01:
declare @TestData table (
AS400Dates int
);
insert into @TestData
values (1080518),
(1080707),
(1080515),
(1080731),
(1080815),
(1080822),
(1080911),
(1080916),
(1080925),
(1080926),
(1080927),
(1081023);
January 19, 2013 at 2:07 pm
Welsh Corgi (1/19/2013)
I'm haveing trouble with a simple CAST to VARCHAR Statement.
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
CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMo
FROM Customer
In the second WHEN, you have LEFT(CAST instead of CAST(LEFT
Any help would be greatly apreciated.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 19, 2013 at 7:12 pm
Yes, I should have provided the information you requested.
I'm upgrading 22 databases from 2005 to 2008 R2 to another Server this weekend.
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 20, 2013 at 3:12 pm
Welsh Corgi (1/19/2013)
Yes, I should have provided the information you requested.I'm upgrading 22 databases from 2005 to 2008 R2 to another Server this weekend.
And you couldn't take 5 minutes to make minor changes to the code I posted to help use better understand your problem and provide you with possible solutions?
January 20, 2013 at 6:27 pm
It seems posting from a tablet makes me sloppy...it should have read like this:
Welsh Corgi (1/19/2013)
I'm haveing trouble with a simple CAST to VARCHAR Statement.
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
CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMo
FROM Customer
Any help would be greatly apreciated.
In the first WHEN clause you have
CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2)))
instead of
CAST(LEFT(Customer.STARTDATE, 2) AS VARCHAR(2))
In the second WHEN, you have
LEFT(CAST(Customer.STARTDATE, 2) AS VARCHAR(2))
instead of
CAST(LEFT(Customer.STARTDATE, 2) AS VARCHAR(2))
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 21, 2013 at 8:43 am
Thanks. Not sure why I'm getting an error on the substring function.
Msg 8116, Level 16, State 1, Line 1
Argument data type numeric is invalid for argument 1 of substring function.
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 8:57 am
Welsh Corgi (1/21/2013)
Thanks. Not sure why I'm getting an error on the substring function.Msg 8116, Level 16, State 1, Line 1
Argument data type numeric is invalid for argument 1 of substring function.
My GUESS would be that STARTDATE is held as a numeric type, but it is only a guess because you still haven't posted any table definition...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 21, 2013 at 9:03 am
Welsh Corgi (1/21/2013)
Thanks. Not sure why I'm getting an error on the substring function.Msg 8116, Level 16, State 1, Line 1
Argument data type numeric is invalid for argument 1 of substring function.
With no ddl we are shooting in the dark but that message is pretty clear. Your code "substring(Customer.STARTDATE, 3, 2)" make me guess that STARTDATE is a datetime? You can't take a substring of any datatype other than character data. You should probably take a look at CONVERT and/or DATEPART.
_______________________________________________________________
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 9:13 am
Why use all those substrings anyways?
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
FROM
@TestData;
January 21, 2013 at 9:24 am
Welsh Corgi (1/19/2013)
I'm haveing trouble with a simple CAST to VARCHAR Statement.
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
CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMo
FROM Customer
Any help would be greatly apreciated.
Code above when parsed returns this:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
A rewrite of the above may look like this:
SELECT
CASE WHEN CAST(LEFT(Customer.STARTDATE, 2) AS VARCHAR(2)) = '98' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2))
WHEN CAST(LEFT(Customer.STARTDATE, 2) AS VARCHAR(2)) = '99' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMo
FROM
dbo.Customer
January 21, 2013 at 9:32 am
try this one............
SELECT
CASE WHEN CAST(substring(Customer.STARTDATE,2,2) AS VARCHAR(2)) = '98' THEN CAST(substring(Customer.STARTDATE, 4, 2) AS VARCHAR(2))
WHEN CAST(substring(Customer.STARTDATE,2,2) AS VARCHAR(2)) = '99' THEN CAST(substring(Customer.STARTDATE, 4, 2) AS VARCHAR(2))
END AS LossMo
FROM Customer
OR
SELECT
CASE WHEN CAST(left(Customer.STARTDATE,2) AS VARCHAR(2)) = '98' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2))
WHEN CAST(left(Customer.STARTDATE,2) AS VARCHAR(2)) = '99' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2))
END AS LossMo
FROM Customer
January 21, 2013 at 9:40 am
Welsh Corgi (1/19/2013)
I'm haveing trouble with a simple CAST to VARCHAR Statement.
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
CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMo
FROM Customer
Any help would be greatly apreciated.
It's roughly equivalent to this:
SELECT
c.STARTDATE,
LossMo = CASE
WHEN x.thingy IN ('98','99') THEN x.AnotherThingy
ELSE NULL END
FROM Customer c
CROSS APPLY (
SELECT
Thingy = CAST(LEFT(c.STARTDATE, 2 AS VARCHAR(2))),
AnotherThingy = CAST(substring(c.STARTDATE, 3, 2) AS VARCHAR(2))
) x
which is a little confusing...
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 21, 2013 at 9:52 am
Welsh Corgi (1/21/2013)
Thanks. Not sure why I'm getting an error on the substring function.Msg 8116, Level 16, State 1, Line 1
Argument data type numeric is invalid for argument 1 of substring function.
Perhaps it is because an integer value does not implicitly convert to a character string.
You may do well to reread that first article you reference in your own signature block and follow the advice in it.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply