InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 12:41:38 GMT20http://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[code="sql"]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[/code]From a purely logical point of view since both conditions lead to the same logical branch it is cleaner to writes [code="sql"]SELECT CASE WHEN LEFT(Customer.StartDate, 2) in ('98','99') THEN substring(convert(varchar(20),Customer.StartDate), 3, 2) END AS LossMo FROM Customer[/code] What do you do when values are not in this small set?Mon, 25 Mar 2013 12:58:07 GMTmmartin1http://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]ChrisM@Work (1/22/2013)[/b][hr][quote][b]Welsh Corgi (1/22/2013)[/b][hr]The following works :[code="sql"]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 [/code]The StartDate column is numeric (7,2).Thanks.[/quote]That one was a curve ball - numeric data won't have a leading zero:[quote][b]Welsh Corgi (1/19/2013)[/b][hr]...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)...[/quote][/quote]Even better, the data he did provide early won't fit in a numeric(7,2) data type:[code="sql"]declare @TestData table ( AS400Dates numeric(7,2));insert into @TestDataVALUES (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 TheMonthFROM @TestData;[/code]Error:Msg 8115, Level 16, State 8, Line 5Arithmetic overflow error converting int to data type numeric.The statement has been terminated.(0 row(s) affected)Tue, 22 Jan 2013 07:09:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/22/2013)[/b][hr]The following works :[code="sql"]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 [/code]The StartDate column is numeric (7,2).Thanks.[/quote]That one was a curve ball - numeric data won't have a leading zero:[quote][b]Welsh Corgi (1/19/2013)[/b][hr]...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)...[/quote]Tue, 22 Jan 2013 06:47:24 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/22/2013)[/b][hr]The following works :[code="sql"]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 [/code]The StartDate column is numeric (7,2).Thanks.[/quote]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.Tue, 22 Jan 2013 06:42:21 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxThe following works :[code="sql"]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 [/code]The StartDate column is numeric (7,2).Thanks.Tue, 22 Jan 2013 06:34:34 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Lynn Pettis (1/21/2013)[/b][hr][quote][b]Welsh Corgi (1/21/2013)[/b][hr]I had to use a CONVERT with the SUBSTRING Function as opposed to the CAST.This works.[code="sql"]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[/code]Thanks.[/quote]Really? When I post the following into SSMS and click parse I get an error:[code="sql"]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[/code]Error:Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.[/quote]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:[code="sql"]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;[/code]Mon, 21 Jan 2013 18:26:12 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/21/2013)[/b][hr]I had to use a CONVERT with the SUBSTRING Function as opposed to the CAST.This works.[code="sql"]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[/code]Thanks.[/quote]Really? When I post the following into SSMS and click parse I get an error:[code="sql"]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[/code]Error:Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.Mon, 21 Jan 2013 13:19:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxI had to use a CONVERT with the SUBSTRING Function as opposed to the CAST.This works.[code="sql"]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[/code]Thanks.Mon, 21 Jan 2013 12:53:59 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx16 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.[code]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[/code]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. [url=http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx]http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx[/url].Mon, 21 Jan 2013 10:20:15 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/21/2013)[/b][hr]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.[/quote]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.Mon, 21 Jan 2013 09:52:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/19/2013)[/b][hr]I'm haveing trouble with a simple CAST to VARCHAR Statement.[code="sql"]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[/code]Any help would be greatly apreciated.[/quote]It's roughly equivalent to this:[code="sql"]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 [/code]which is a little confusing...Mon, 21 Jan 2013 09:40:02 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxtry 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 CustomerMon, 21 Jan 2013 09:32:11 GMTmdsharif532http://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/19/2013)[/b][hr]I'm haveing trouble with a simple CAST to VARCHAR Statement.[code="sql"]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[/code]Any help would be greatly apreciated.[/quote]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:[code="sql"]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[/code]Mon, 21 Jan 2013 09:24:28 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxWhy use all those substrings anyways?[code="sql"]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;[/code]Mon, 21 Jan 2013 09:13:09 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/21/2013)[/b][hr]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.[/quote]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.Mon, 21 Jan 2013 09:03:40 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/21/2013)[/b][hr]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.[/quote]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...Mon, 21 Jan 2013 08:57:40 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxThanks. 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.Mon, 21 Jan 2013 08:43:20 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxIt seems posting from a tablet makes me sloppy...it should have read like this:[quote][b]Welsh Corgi (1/19/2013)[/b][hr]I'm haveing trouble with a simple CAST to VARCHAR Statement.[code="sql"]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[/code]Any help would be greatly apreciated.[/quote]In the first WHEN clause you have[code="sql"]CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2)))[/code]instead of[code="sql"]CAST(LEFT(Customer.STARTDATE, 2) AS VARCHAR(2))[/code]In the second WHEN, you have [code="sql"]LEFT(CAST(Customer.STARTDATE, 2) AS VARCHAR(2))[/code] instead of [code="sql"]CAST(LEFT(Customer.STARTDATE, 2) AS VARCHAR(2))[/code]Sun, 20 Jan 2013 18:27:18 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/19/2013)[/b][hr]Yes, I should have provided the information you requested.I'm upgrading 22 databases from 2005 to 2008 R2 to another Server this weekend.[/quote]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?Sun, 20 Jan 2013 15:12:18 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxYes, I should have provided the information you requested.I'm upgrading 22 databases from 2005 to 2008 R2 to another Server this weekend.Sat, 19 Jan 2013 19:12:47 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspx[quote][b]Welsh Corgi (1/19/2013)[/b][hr]I'm haveing trouble with a simple CAST to VARCHAR Statement.[code="sql"]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[/code]In the second WHEN, you have LEFT(CAST instead of CAST(LEFTAny help would be greatly apreciated.[/quote]Sat, 19 Jan 2013 14:07:08 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxAre 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:[code="sql"]declare @TestData table ( AS400Dates int);insert into @TestDatavalues (1080518), (1080707), (1080515), (1080731), (1080815), (1080822), (1080911), (1080916), (1080925), (1080926), (1080927), (1081023);[/code]Sat, 19 Jan 2013 11:35:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxI 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.[code="plain"]108051810807071080515108073110808151080822108091110809161080925108092610809271081023[/code]Sat, 19 Jan 2013 09:46:45 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1409205-392-1.aspxI'm haveing trouble with a simple CAST to VARCHAR Statement.[code="sql"]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[/code]Any help would be greatly apreciated.Sat, 19 Jan 2013 09:24:15 GMTWelsh Corgi