SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with CAST to VARCHAR with SUBSTRING Function


Problem with CAST to VARCHAR with SUBSTRING Function

Author
Message
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10182 Visits: 4894
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/
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10182 Visits: 4894
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39358 Visits: 38543
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);




Cool
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)
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4124 Visits: 7865
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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Welsh Corgi
    Welsh Corgi
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10182 Visits: 4894
    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
    Lynn Pettis
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39358 Visits: 38543
    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?

    Cool
    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)
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4124 Visits: 7865
    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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Welsh Corgi
    Welsh Corgi
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10182 Visits: 4894
    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
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4124 Visits: 7865
    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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Sean Lange
    Sean Lange
    One Orange Chip
    One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

    Group: General Forum Members
    Points: 26113 Visits: 17538
    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.

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


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search