Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Problem with CAST to VARCHAR with SUBSTRING Function Expand / Collapse
Author
Message
Posted Saturday, January 19, 2013 9:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1409205
Posted Saturday, January 19, 2013 9:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1409209
Posted Saturday, January 19, 2013 11:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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);





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 #1409219
Posted Saturday, January 19, 2013 2:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 1,805, Visits: 5,864
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1409236
    Posted Saturday, January 19, 2013 7:12 PM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, September 29, 2014 10:57 PM
    Points: 4,242, Visits: 4,290
    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/

    Post #1409246
    Posted Sunday, January 20, 2013 3:12 PM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Today @ 1:42 AM
    Points: 20,799, Visits: 32,717
    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?



    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 #1409315
    Posted Sunday, January 20, 2013 6:27 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:19 PM
    Points: 1,805, Visits: 5,864
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1409334
    Posted Monday, January 21, 2013 8:43 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, September 29, 2014 10:57 PM
    Points: 4,242, Visits: 4,290
    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/

    Post #1409590
    Posted Monday, January 21, 2013 8:57 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:19 PM
    Points: 1,805, Visits: 5,864
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1409596
    Posted Monday, January 21, 2013 9:03 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 6:31 PM
    Points: 13,207, Visits: 12,688
    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 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 #1409598
    « Prev Topic | Next Topic »

    Add to briefcase 123»»»

    Permissions Expand / Collapse