Problem with CAST to VARCHAR with SUBSTRING Function

Question

Post reply

Problem with CAST to VARCHAR with SUBSTRING Function

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 19, 2013 at 9:24 am

#287825

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/
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

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);

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

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);

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

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/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

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);

Sean Lange SSC Guru Points: 286601 More actions · Answer 9

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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

mdsharif532 Old Hand Points: 367 More actions · Answer 12

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

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.

Problem with CAST to VARCHAR with SUBSTRING Function

Cookies on SQLServerCentral