Need help with converting a query to maxDB

[Former Member]

Hello,

I'm experienced in SQL Server however not yet familiar with the MaxDB functions very well.

I need to convert the following WHERE clause in a query to one that can run on MaxDB:

WHERE DATEADD(MILLISECOND, Timestamp, '19000101') >= DATEADD(YEAR, DATEDIFF(YEAR, '19000101', DATEADD(DAY, -10, GETDATE())), '19000101')
   AND DATEADD(MILLISECOND, Timestamp, '19000101') < DATEADD(DAY, -9, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'))

Rgds,

Roy

[Former Member]

Hi,

what are you talking about?

Did you see that your TIMESTAMP-column is of datatype FIXED ? Funny, funny, I can't believe.

And what I was talking about is the Date and Time Format (datetimeformat) , not the datatype of a column.

Elke

[Former Member]

Like I said, I'm making my first steps with MaxDB.

You said that I may have to change the time-value '00000000' to the date-time-format that I am using.

Since I'm not the one who created these tables I have no idea what the format is and was wondering how to check this, I can submit a sample value from the db if it helps...

[Former Member]

Hi,

as you surely have thought of by yourselve...

substitute

TIMESTAMP (MAKEDATE(YEAR(DATE),1), '00000000')

by

TIMESTAMP (MAKEDATE(YEAR(SUBDATE(DATE,9)),1), '00000000')

and it will do even on 5th of January

Elke

[Former Member]

Thanks Elke.

How do I check the time format used on a field in MaxDB?

Using the Studio I see that the field is of type FIXED

Roy

[Former Member]

Hi,

use

TIMESTAMP (MAKEDATE(YEAR(DATE),1), '00000000')

and

TIMESTAMP (SUBDATE(DATE,9), '00000000')

Perhaps you will have to change the time-value '00000000' to the date-time-format you are using.

And perhaps you will have to change 9 to 10 if you do not want to include the current day as 10th day.

And you really want NO result during the first 9 days of the year?

Elke

[Former Member]

Hi Elke,

Thanks, I'll try that.

I don't want any data from the last 9 days not from the first 9 days of the year, so if today is 17/5/2010 the BETWEEN will be: 1/1/2010 and 8/5/2010 and if today was 10/2/2010 for example the BETWEEN would have been: 1/1/2010-1/2/2010.

HOWEVER, if today was 5/1/2010 then the BETWEEN should automatically adjust itself to 2009 and check for: 1/1/2009-27/12/2009 since when rolling 5/1/2010 10 days backwards we're still in 2009, so the query needs to first rollback and then make the dates reference.

I don't believe that what you have provided answers these requirements but than again I might be wrong...

Rgds,

Roy

[Former Member]

And by the way the timestamp field name is MODIFICATIONDATE so we'll be on the same page for code samples.

Edited by: Roy Cohen on May 17, 2010 1:52 PM

[Former Member]

... or instead of giving a lengthy explanation you might want to have a look at the fine manual,

keyword date function :

https://cw.sdn.sap.com/cw/docs/DOC-41085

regards

[Former Member]

Hi,

would you mind telling us what this where-clause should do as we here are familiar with SAP MaxDB, but not so much with SQL Server...

Elke

[Former Member]

I have looked at the manual but a bit struggling with it which is why I posted it here.

Basically the WHERE clause is checking a TIMESTAMP field and taking only those that are between the beginning of the current year (which is checked automatically and not hard-coded) and 10 days before the current day (which is also checked automatically).

Rgds,

Roy