Cast / Substring Func not working

Question

Post reply

Cast / Substring Func not working

trudye10

SSCommitted

Points: 1721
More actions
October 17, 2009 at 1:33 pm

#134518

Hi Guys, having a little trouble with the following func.:
select CAST(SUBSTRING(CEFDT, 5, 2) AS varchar) as Eff_Date
FROM DBO.DtTbl
CEFDT is a date with a format of yyyymmdd that I need to convert to mmddyyyy. The Right/Left functions are working fine this one is giving me grief. I am receiving the following msg:
Argument data type numeric is invalid for argument 1 of substring function.
Any idea how I can fix this one?
Thanx much

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This might give you an idea, as it is, it is rather crude

DECLARE @CEFDT DATETIME

SET DATEFORMAT ymd

SET @CEFDT = '20091017'

DECLARE @It VARCHAR(15)

DECLARE @is VARCHAR(2)

SET @It = (SELECT DATEPART(yy , @CEFDT))

SET @is = (SELECT DATEPART(mm,@CEFDT))

SELECT @It + @is

but it just might re-generate your creative juices.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Hey Trudye:

You need to think about this a little. Your [CEFDT] is NOT really a date. It is a numeric column holding a bunch of numbers that might be interpreted as a date. In SQL, the datetime datatype gives you a DATEPARt() function which will break out the year, month and day.

Unlike LEFT() and RIGHT(), the SUBSTRING() function has to work on datatypes that contain character strings (char, varchar, etc). A numeric datatype is not one of these string datatype, and your error message says exactly that: the first parameter can't be numeric (or int or float).

However, CEFDT can be CAST or CONVERTed into a varchar. The code you posted tries to CAST the result as a varchar, but the SUBSTRING() function fails before it can return a result.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 3

bitbucket showed you the way to extract parts from a date/datetime datatyped column.

In all other cases I advise to your convert to put date/datetime datatyped columns is a certain way.

Convert lets you choose a desired output (or input if you go back to datetime)

Check out books online.http://msdn.microsoft.com/en-us/library/ms187928.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

trudye10 SSCommitted Points: 1721 More actions · Answer 4

Thanx so much to all of you. I knew about the datepart function from VBA and I should

have explored it before posting. I was under the false memory that the date had to be

in the format of mmddyyyy. What bibucket showed (and I should have remembered) was I could set the format.

Bob I have a question for you. How should I have formatted the Convert/Substring functions or

is that a futile effort/oxymoron?

Thanks again guys and have a great weekend. As usual you have been a tremendous help.

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 5

maybe this ref will shed a good light on the issue.......

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Trudye, I tried to give you a hint that you were casting the result. This will work with your current column which is in numeric format. First you cast the numeric column as varchar, then you apply the substring function. Remember, when you are nesting functions, you read the order of execution from the inside out.

declare @CEFDT numeric(8,0)

set @CEFDT = 20091013

select SUBSTRING(CAST(@CEFDT AS varchar), 5, 2) as Eff_Date

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

trudye10 SSCommitted Points: 1721 More actions · Answer 7

Thanks so much Bob, after re-reading your response I got it. I wrote the code this morning and tested. I works Great!!!