Problem with CAST to VARCHAR with SUBSTRING Function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Problem with CAST to VARCHAR with SUBSTRING...

24 posts, Page 2 of 3

««1 2 3 »»

Problem with CAST to VARCHAR with SUBSTRING Function

Rate Topic

Display Mode

Topic Options

Author

Message

Lynn Pettis

Posted Monday, January 21, 2013 9:13 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1409602

Lynn Pettis

Posted Monday, January 21, 2013 9:24 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

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

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1409605

mdsharif532

Posted Monday, January 21, 2013 9:32 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:09 PM
Points: 14, Visits: 97

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

Post #1409611

ChrisM@Work

Posted Monday, January 21, 2013 9:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1409617

Lynn Pettis

Posted Monday, January 21, 2013 9:52 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1409628

Sean Lange

Posted Monday, January 21, 2013 10:20 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1409638

Welsh Corgi

Posted Monday, January 21, 2013 12:53 PM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 3,820, Visits: 4,044

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/

Post #1409683

Lynn Pettis

Posted Monday, January 21, 2013 1:19 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

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

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1409690

Lynn Pettis

Posted Monday, January 21, 2013 6:26 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

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;

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1409761

Welsh Corgi

Posted Tuesday, January 22, 2013 6:34 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 3,820, Visits: 4,044

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/

Post #1409997

« Prev Topic | Next Topic »

24 posts, Page 2 of 3

««1 2 3 »»

Permissions