Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1678
  • Last Modified:

How to compare datetime in SQL to Remedy/Unix datetime?

I'm writing a DTS package which queries a Remedy database. Datetime in Remedy is handled as seconds since 1 January 1970. My question is, how do I compare SQL datetime to Remedy datetime?

SELECT * FROM RemedyTable WHERE getdate() >= [datefield]

I tried to convert the getdate() into seconds using DATEDIFF, but I don't think this can run on Remedy.

Any ideas?
0
TimHayes
Asked:
TimHayes
  • 3
  • 3
1 Solution
 
adatheladCommented:
The query I'd use would be:
SELECT * FROM RemedyTable WHERE DateField <= DATEDIFF(ss, '1970-01-01 00:00:00', GETDATE())

If you're saying that doesn't work, try this (I cant see why it wouldnt work - this next example is practically the same really).

DECLARE @pdecConvertedTime DECIMAL(18,0)
SELECT @pdecConvertedTime = DATEDIFF(ss, '1970-01-01 00:00:00', GETDATE())

SELECT * FROM RemedyTable
WHERE DateField <= @pdecConvertedTime


Cheers
0
 
TimHayesAuthor Commented:
This doesn't seem to work, because I'm writing a DTS package it needs to run in the Remedy/Oracle environment rather than the SQL Server environment. So, whilst SQL Server parses the query correctly, it won't run correctly in Remedy.

Any other ideas?
0
 
adatheladCommented:
ah ok, I'm unfamiliar with the Remedy/Oracle environment so I don't know the query syntax.

What I was trying to suggest, is that you format the current date/time in the DTS package in SQL into seconds since Jan 1 1970, before passing that value into the query on the Remedy database. i.e. as opposed to performing the date manipulation inside the actual query which fails.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TimHayesAuthor Commented:
That's definitely the way to do it, but because I'm writing a DTS package all the declarations and formatting of variables is done in the same part as the SQL query itself.

Unless you know of a way to pass a variable to a DTS package...?
0
 
adatheladCommented:
0
 
TimHayesAuthor Commented:
That's definitely the way to do it, but because I'm writing a DTS package all the declarations and formatting of variables is done in the same part as the SQL query itself.

Unless you know of a way to pass a variable to a DTS package...?
0
 
CleanupPingCommented:
TimHayes:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now