try to compare 2 datetime! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

try to compare 2 datetime!

Author

Topic

loango
Starting Member

2 Posts

Posted - 2008-10-06 : 07:40:10

Hi,
I try to run the above query:
SELECT Pays, Allotment, Dateobten, Datexpir, Alloted, Disburs
FROM ExpenseSnapshoot
WHERE (Dateobten >= CONVERT(VARCHAR(25),CURRENT_TIMESTAMP,21));

But I will get the following message:
"Conversion failed when converting datetime from character string"! It works fine under SQL Server 2000. I have been "googled" for an answer, for 3 days. So, I will appreciate any help!
Cheers,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 07:43:03

just use this

SELECT Pays, Allotment, Dateobten, Datexpir, Alloted, Disburs
FROM ExpenseSnapshoot
WHERE Dateobten >=dateadd(dd,datediff(dd,0,current_timestamp),0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 08:35:11

What datatype is dateObten column?

E 12°55'05.63"
N 56°04'39.26"

loango
Starting Member

2 Posts

Posted - 2008-10-06 : 08:42:32

Hi Visakh 16,
Thamks for your prompt reply. Unfortunately, I have still got the same error message when running the above query:
SELECT Pays, Allotment, Dateobten, Datexpir, Alloted, Disburs
FROM ExpenseSnapshoot
WHERE Dateobten >=dateadd(dd,datediff(dd,0,current_timestamp),0)

Cheers,

NB: DateObten is Datetime type! And I am using the 121 (21) format.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 09:36:52

is this happening when you pass value from application or directly running query in query analyser?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 09:51:30

SELECT Pays, Allotment, Dateobten, Datexpir, Alloted, Disburs
FROM ExpenseSnapshoot
WHERE Dateobten >= datediff(day, 0, getdate())

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 09:53:57

quote:
Originally posted by Peso

SELECT Pays, Allotment, Dateobten, Datexpir, Alloted, Disburs
FROM ExpenseSnapshoot
WHERE Dateobten >= datediff(day, 0, getdate())

E 12°55'05.63"
N 56°04'39.26"

will that make a difference? any difference between using current_timestamp and getdate() functions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 09:55:18

Depends on what RDBMS he is using.
OP wrote "it works find under SQL 2000"...

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 09:57:56

quote:
Originally posted by Peso

Depends on what RDBMS he is using.
OP wrote "it works find under SQL 2000"...

E 12°55'05.63"
N 56°04'39.26"

Ok. does that mean current_timestamp might have different behaviour in different dbs?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 10:02:19

Yes. On PostGreSQL it seems that time zone is included like this

2008-10-06 15:48:21-08

WIth Oracle PL/SQL You get

10-Sep-05 10.58.24.853421 PM -07:00

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 10:05:07

quote:
Originally posted by Peso

Yes. On PostGreSQL it seems that time zone is included like this
2008-10-06 15:48:21-08

WIth Oracle PL/SQL You get
10-Sep-05 10.58.24.853421 PM -07:00

E 12°55'05.63"
N 56°04'39.26"

Ok thanks Peso. wasnt aware of that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources