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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
alpmoonConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.