Porting Sample Oracle 10gR2 SQL to Sybase ASE 15.x query

Hi everyone:

I have a background in Java / Oracle - and am new to Sybase ASE.

I am trying to port couple of SQL queries from Oracle 10gR2 to Sybase ASE 15.x (latest edition download) - could anyone please suggest the sybase equivalent for following Oracle 10gR2 query - I've simplified the query to get a test case.

Basically, crux of the problem is date time arithmetic and char to date conversion - which I am not getting right after an hour of effort.

Thanks,
Tapasvi


SELECT
	to_char(greatest(max(to_date(created_date,'YYYYMMDDHH24MI')),
                         max(to_date(modified_date,'YYYYMMDDHH24MI'))
                        ),
                'dd/mm/yyyy hh24:mi:ss'
               )
FROM
        sample_table
WHERE
        (
        	( to_date(created_date,'YYYYMMDDHH24MI')>=trunc(sysdate-7) )
	        or
        	( modified_date is not null and to_date(modified_date,'YYYYMMDDHH24MI')>=trunc(sysdate-7) )
	)

Open in new window

LVL 4
tapasviAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alpmoonCommented:
I don't know Oracle much, but I will write it based of my guesses about Oracle functions. I think you need to use case instead of greatest function:

SELECT case
           when created_date > modified_date then convert(char, created_date, 103)+' '+convert(char, created_date, 8)
           else convert(char, modified_date , 103)+' '+convert(char, modified_date , 8)
FROM
        sample_table
WHERE
        (
                ( created_date >= dateadd(dd, -7, getdate()) )
-- assuming that you want to subtract 7 days from today
                or
                ( modified_date is not null and modified_date >= dateadd(dd, -7, getdate())  )
        )
0
tapasviAuthor Commented:
Hi Alpmoon:

I should have mentioned that the created_date and modified_date are varchar columns. They don't have date data type and they have values in YYYYMMDDHH24MI format - so , "26th March, 2010 2 PM" is stored as ''201003261400"

So, I need to convert that string into date and then compare the dates...to_date function in oracle converts varchar to date data type based on the format mask given as second argument...My problem is, I am not finding the equivalent format mask ( style number ) in convert function which can accept values in YYYYMMDDHH24MI format and convert to date.


0
alpmoonCommented:
You don't need to choose any date format to convert a string to datetime. Just convert your string to an understandable format:

SELECT case
           when created_date > modified_date  then
                      convert(char, convert(datetime, left(created_date, 8)), 103)+' '+convert(char, convert(datetime, substring(created_date,9,2)+':'+right(created_date,2)), 8)
           else convert(char, convert(datetime, left(modified_date , 8)), 103)+' '+convert(char, convert(datetime, substring(modified_date ,9,2)+':'+right(modified_date, 2)), 8)
FROM
        sample_table
WHERE
        (
                ( convert(datetime, left(created_date, 8)+' '+substring(created_date,9,2)+':'+right(created_date,2)) >= dateadd(dd, -7, getdate()) )
-- assuming that you want to subtract 7 days from today
                or
                ( modified_date is not null and convert(datetime, left(modified_date, 8)+' '+substring(modified_date,9,2)+':'+right(modified_date,2))  >= dateadd(dd, -7, getdate())  )
        )

Nevertheless, I think you should use a datetime column instead of char column for that purpose.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.