Hello list,

something I have noticed, when using the $DATE$ keyword over at some FB5.1 variable qualification.


Some qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = $DATE$)

Translates over at the SQL to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 1059343200))

What is interesting is the 1059343200. When using a formula to translate it to the "normal" date, it would actually be something like "28.07.2003 23:00". ...saying that, the system uses the full timestamp (at least to my understanding), including the time.

The query then of course is not able to find what is intented.


If using a qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = "07/28/2003)

Translates to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 2452849))


I do not know what format "2452849" is (SQL Date variable?). However that one works.


Well, just wanted pass that one to the list, if anyone has noticed that with the $DATE$ keyword already, if that is by design, if that is FB specific only, or if I got something wrong :-)



Thanks!
Robert


.........................................................
Mit freundlichem Gru? / kind regards
Robert Kern
Siemens VDO Automotive AG
Customer Services
Sodener Str. 9
65824 Schwalbach
Tel. +49 6196 87 2546
Mob. +49 170 8522 515
Fax. +49 6196 87 79 2546
E-Mail: kernrobert@siemens.com

Don't know about flashboards, but we were told in Remedy training (and I
have seen it in practice) that if you give the ARSystem only a "date", it
defaults the time to Midnight. So, a search tat says 'Creation Date
Report' = $DATE$ would return records ONLY where the 'Creation Date Report'
= at Midnight.

If I wanted to search a form for all records created yesterday, I would
have to say something like this:

'Create Date' >= "07/27/2003" and 'Create Date' < $DATE$

So, say = $DATE$ ONLY if you care about records created EXACTLY at
midnight.... If you want a date range, split it into 2 statements joined
by an and.

Matt



"Kern Robert
(SBA)" To: ARSLIST@ARSLIST.ORG
ENS.COM> Subject: $DATE$ translating to Timestamp ?
Sent by: "Action
Request System
discussion
list(ARSList)"
.ORG>


07/28/2003 12:29
PM
Please respond
to arslist






Hello list,

something I have noticed, when using the $DATE$ keyword over at some FB5.1
variable qualification.


Some qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = $DATE$)

Translates over at the SQL to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 =
1059343200))

What is interesting is the 1059343200. When using a formula to translate it
to the "normal" date, it would actually be something like "28.07.2003
23:00". ...saying that, the system uses the full timestamp (at least to my
understanding), including the time.

The query then of course is not able to find what is intented.


If using a qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report'
= "07/28/2003)

Translates to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 =
2452849))


I do not know what format "2452849" is (SQL Date variable?). However that
one works.


Well, just wanted pass that one to the list, if anyone has noticed that
with the $DATE$ keyword already, if that is by design, if that is FB
specific only, or if I got something wrong :-)



Thanks!
Robert


.........................................................
Mit freundlichem Gru? / kind regards
Robert Kern
Siemens VDO Automotive AG
Customer Services
Sodener Str. 9
65824 Schwalbach
Tel. +49 6196 87 2546
Mob. +49 170 8522 515
Fax. +49 6196 87 79 2546
E-Mail: kernrobert@siemens.com



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

Your date field is functioning correctly. What will happen is because Remedy automatically appends the time to be 12:00 AM, when a search is done on the Date Only field it should append the time 12:00 AM, in sense making the time irrelevant because every date recorded to the field should have the time of 12:00 AM.

Thank you,

Rick McCabe
Remedy Consultant
Intellimark
RMcCabe@Intellimark-IT.com
Voice: (717)790-0404 ext. 4487
Cell: (717)919-4729
Fax: (717)795-9153
www.intellimark-it.com


-----Original Message-----
From: Kern Robert (SBA) [mailto:kernrobert@SIEMENS.COM]
Sent: Monday, July 28, 2003 12:29 PM
To: ARSLIST@ARSLIST.ORG
Subject: $DATE$ translating to Timestamp ?

Hello list,

something I have noticed, when using the $DATE$ keyword over at some FB5.1 variable qualification.


Some qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = $DATE$)

Translates over at the SQL to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 1059343200))

What is interesting is the 1059343200. When using a formula to translate it to the "normal" date, it would actually be something like "28.07.2003 23:00". ...saying that, the system uses the full timestamp (at least to my understanding), including the time.

The query then of course is not able to find what is intented.


If using a qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = "07/28/2003)

Translates to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 2452849))


I do not know what format "2452849" is (SQL Date variable?). However that one works.


Well, just wanted pass that one to the list, if anyone has noticed that with the $DATE$ keyword already, if that is by design, if that is FB specific only, or if I got something wrong :-)



Thanks!
Robert


.........................................................
Mit freundlichem Gru? / kind regards
Robert Kern
Siemens VDO Automotive AG
Customer Services
Sodener Str. 9
65824 Schwalbach
Tel. +49 6196 87 2546
Mob. +49 170 8522 515
Fax. +49 6196 87 79 2546
E-Mail: kernrobert@siemens.com

Hi there and thanks for the replies,

just wanted to add a little to that one. Running on 5.1.1 patch 1220 SQL 2k btw. Don't no wether that might have an impact on how date fields are handled.


"making the time irrelevant because every date recorded to the field should have the time of 12:00 AM"

I'm not that sure on that one. When looking over at the table how things are stored over at the DB and check the column from the field of data type "Date", entries are stored fe. like:

2452849

That should be the number of days since 1 January 4713 BCE (like I have learned yestarday :-). Therefore the unix timestamp is actually not being used like it seems (and that is perfectly like it should be, at least to my understanding).

Therefore when running the search: 'Date Field' = "07/28/2003" - and SQL converting the date to that format (2452849 over at the example), that is just fine and the statement is working.

However when using the keyword, 'Date Field' = $DATE$, and SQL translating it to the unix timestamp (1059343200), of course there can't be a hit (if like using the unix timestamp, I could have done that in the first place as well - like using timestamp keyword - but that is not the desired option :-)



"If I wanted to search a form for all records created yesterday, I would have to say something like this:
'Create Date' >= "07/27/2003" and 'Create Date' < $DATE$"

That of course like what has to be done, but I would more like consider it a workaround. As I can't use a fixed date (as the search is being run every day automatically), I can use sometime like $DATE$ minus 24 hours or something like that.

I think that the search in the example, is actually working, but to my understanding translates to something like this:

Create Date = Field of type Date = 2452849 over at the DB (fe)

'Create Date' >= "07/27/2003" - translates to something like - 'Create Date' >= 2452849 (fe)
'Create Date' < $DATE$" - translates to something like - 'Create Date' < 1059343200


So, to my understanding, the query in the example acutally really has the desired output, but the last part of the range ist just way in the future (which does not matter in this case, as when running the search over at acutal date, it will ever be proper.


Uh, does that make sense? I'm not a SQL pro or something, so perhaps everything said is just nonsense. ...just trying to understand the whole thing :-)


Greetings,
Robert


.........................................................
Mit freundlichem Gru? / kind regards
Robert Kern
Siemens VDO Automotive AG
Customer Services
Sodener Str. 9
65824 Schwalbach
Tel. +49 6196 87 2546
Mob. +49 170 8522 515
Fax. +49 6196 87 79 2546
E-Mail: kernrobert@siemens.com





-----Ursprungliche Nachricht-----
Von: Richard McCabe [mailto:rmccabe@INTELLIMARK-IT.COM]
Gesendet: Montag, 28. Juli 2003 19:12
An: ARSLIST@ARSLIST.ORG
Betreff: Re: $DATE$ translating to Timestamp ?


Your date field is functioning correctly. What will happen is because Remedy automatically appends the time to be 12:00 AM, when a search is done on the Date Only field it should append the time 12:00 AM, in sense making the time irrelevant because every date recorded to the field should have the time of 12:00 AM.

Thank you,

Rick McCabe
Remedy Consultant
Intellimark
RMcCabe@Intellimark-IT.com
Voice: (717)790-0404 ext. 4487
Cell: (717)919-4729
Fax: (717)795-9153
www.intellimark-it.com


-----Original Message-----
From: Kern Robert (SBA) [mailto:kernrobert@SIEMENS.COM]
Sent: Monday, July 28, 2003 12:29 PM
To: ARSLIST@ARSLIST.ORG
Subject: $DATE$ translating to Timestamp ?

Hello list,

something I have noticed, when using the $DATE$ keyword over at some FB5.1 variable qualification.


Some qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = $DATE$)

Translates over at the SQL to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 1059343200))

What is interesting is the 1059343200. When using a formula to translate it to the "normal" date, it would actually be something like "28.07.2003 23:00". ...saying that, the system uses the full timestamp (at least to my understanding), including the time.

The query then of course is not able to find what is intented.


If using a qualification like:
('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = "07/28/2003)

Translates to:
[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 2452849))


I do not know what format "2452849" is (SQL Date variable?). However that one works.


Well, just wanted pass that one to the list, if anyone has noticed that with the $DATE$ keyword already, if that is by design, if that is FB specific only, or if I got something wrong :-)



Thanks!
Robert


.........................................................
Mit freundlichem Gru? / kind regards
Robert Kern
Siemens VDO Automotive AG
Customer Services
Sodener Str. 9
65824 Schwalbach
Tel. +49 6196 87 2546
Mob. +49 170 8522 515
Fax. +49 6196 87 79 2546
E-Mail: kernrobert@siemens.com

On Mon, 28 Jul 2003 18:29:11 +0200, Kern Robert (SBA) wrote:

>Hello list,
>
>something I have noticed, when using the $DATE$ keyword over at some FB5.1 variable qualification.
>
>
>Some qualification like:
>('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = $DATE$)
>
>Translates over at the SQL to:
>[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 = 1059343200))
>
>What is interesting is the 1059343200. When using a formula to translate it to the "normal" date, it would actually be something like "28.07.2003 23:00". ...saying that, the system uses the full timestamp (at least to my understanding), including the time.

You don't say what the date was when you ran it but it looks like it
defaulted to midnight and then got set back an hour for DST or
something.

Something to watch when checking timestamps at the SQL level is that
they are accurate to seconds - but remedy workflow comparisons appear
to work to the nearest minute - or maybe it was rounded down to the
nearest minute I forget - but I do know that on 4.0.5 I was not
finding the same number of records with a SQL query that the User
tool would find with *apparently* the same query.

HTH

--
Regards

Dave Saville

>but I do know that on 4.0.5 I was not
>finding the same number of records with
> a SQL query that the User
>tool would find with *apparently* the same query.
Don't forget the user tool does a time conversion so I see local date time.
When you do a SQL statement, you see the GMT dates that the server converts
everything to prior to storage......

Matt



Dave Saville
LWORLD.COM> cc:
Sent by: "Action Subject: Re: $DATE$ translating to Timestamp ?
Request System
discussion
list(ARSList)"
.ORG>


07/31/2003 03:26
AM
Please respond
to arslist






On Mon, 28 Jul 2003 18:29:11 +0200, Kern Robert (SBA) wrote:

>Hello list,
>
>something I have noticed, when using the $DATE$ keyword over at some FB5.1
variable qualification.
>
>
>Some qualification like:
>('Created by group' = "RBG HelpDesk") AND ('Creation Date Report' = $DATE
$)
>
>Translates over at the SQL to:
>[...] WHERE ((T398.C536870946 = 'RBG HelpDesk') AND (T398.C536871082 =
1059343200))
>
>What is interesting is the 1059343200. When using a formula to translate
it to the "normal" date, it would actually be something like "28.07.2003
23:00". ...saying that, the system uses the full timestamp (at least to my
understanding), including the time.

You don't say what the date was when you ran it but it looks like it
defaulted to midnight and then got set back an hour for DST or
something.

Something to watch when checking timestamps at the SQL level is that
they are accurate to seconds - but remedy workflow comparisons appear
to work to the nearest minute - or maybe it was rounded down to the
nearest minute I forget - but I do know that on 4.0.5 I was not
finding the same number of records with a SQL query that the User
tool would find with *apparently* the same query.

HTH

--
Regards

Dave Saville



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com