Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Problem with CAST to VARCHAR with SUBSTRING Function Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 21, 2013 9:13 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:12 PM Points: 22,092, Visits: 29,013
 Why use all those substrings anyways?`declare @TestData table ( AS400Dates VARCHAR(10));insert into @TestDataVALUES ('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) DateAsDateFROM @TestData;`
Post #1409602
 Posted Monday, January 21, 2013 9:24 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:12 PM Points: 22,092, Visits: 29,013
 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 LossMoFROM Customer`Any help would be greatly apreciated.Code above when parsed returns this:Msg 156, Level 15, State 1, Line 2Incorrect 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 LossMoFROM dbo.Customer`
Post #1409605
 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 LossMoFROM CustomerORSELECT 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 LossMoFROM Customer
Post #1409611
 Posted Monday, January 21, 2013 9:40 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:56 AM Points: 6,276, Visits: 12,091
 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 LossMoFROM 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 cCROSS 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1409617
 Posted Monday, January 21, 2013 9:52 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:12 PM Points: 22,092, Visits: 29,013
 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 1Argument 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.
Post #1409628
 Posted Monday, January 21, 2013 10:20 AM
 SSChampion Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:23 PM Points: 10,854, Visits: 10,012
 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 LossMoFROM 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 Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1409638
 Posted Monday, January 21, 2013 12:53 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, December 04, 2013 6:53 AM Points: 3,913, Visits: 4,118
 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 LosstMoFROM 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
 Posted Monday, January 21, 2013 1:19 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:12 PM Points: 22,092, Visits: 29,013
 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 LosstMoFROM 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 LosstMoFROM Customer`Error:Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.
Post #1409690
 Posted Monday, January 21, 2013 6:26 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:12 PM Points: 22,092, Visits: 29,013
 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 LosstMoFROM 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 LosstMoFROM Customer`Error:Msg 156, Level 15, State 1, Line 2Incorrect 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 @TestDataVALUES ('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 TheMonthFROM @TestData;`
Post #1409761
 Posted Tuesday, January 22, 2013 6:34 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, December 04, 2013 6:53 AM Points: 3,913, Visits: 4,118
 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

 Permissions