Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » SQL Server 2008 » T-SQL (SS2K8) » Problem with CAST to VARCHAR with SUBSTRING...
Add to briefcase
24 posts, Page 1 of 3
123»»»

Problem with CAST to VARCHAR with SUBSTRING Function Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Welsh Corgi
Posted Saturday, January 19, 2013 9:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,833, Visits: 4,052
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
Welsh Corgi
Posted Saturday, January 19, 2013 9:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,833, Visits: 4,052
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
Lynn Pettis
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 @ 12:11 AM
Points: 21,602, Visits: 27,431
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
mister.magoo
Posted Saturday, January 19, 2013 2:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 1,289, Visits: 3,859
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
Post #1409236
Welsh Corgi
Posted Saturday, January 19, 2013 7:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,833, Visits: 4,052
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
Lynn Pettis
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 @ 12:11 AM
Points: 21,602, Visits: 27,431
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
mister.magoo
Posted Sunday, January 20, 2013 6:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 1,289, Visits: 3,859
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
Post #1409334
Welsh Corgi
Posted Monday, January 21, 2013 8:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,833, Visits: 4,052
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
mister.magoo
Posted Monday, January 21, 2013 8:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 1,289, Visits: 3,859
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
Post #1409596
Sean Lange
Posted Monday, January 21, 2013 9:03 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233
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
Post #1409598
« Prev Topic | Next Topic »

Add to briefcase
24 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.