Mid Function against SQL2000

[jake29]

I am trying to do something that seems simple.
I want to parse a int field. It always contains 8 characters. The field is used to hold a date yyyymmdd
I need to pull the characters 5 and 6. I can pull the first 4 (Left()) and last 2 (Right()) but the middle 2? Mid() would be the perfect match except I am running this against a SQL2K box.

<cfquery datasource="invoice" name="getInvDate">
select ord_dt, left(ord_dt, 4) as y, mid(ord_dt,5,6) as m
from dbEORDHDR
where slspsn_no = 'I01'
order by y, m
</cfquery>

Any ideas??

 

[r937]

select ord_dt
, left(ord_dt, 4) as y
, substring(ord_dt,5,2) as m



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[jake29]

Yes thanks, I tried that. The problem is it's datatype is a integer. So the results come back as invalid.

 

[r937]

select ord_dt
, left(
cast(ord_dt as varchar(8))
, 4) as y
, substring(
cast(ord_dt as varchar(8))
,5,2) as m

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[jake29]

Thank you r937.

That is it. I had look at Cast, but never used it before.

Again thanks.