datediff

[Naoise]

TableA
================
LastRun <--- datatype datetime

Query

SELECT datediff("hh",getdate(),LastRun) FROM TableA

returns...

696 which is incorrect. Do I need to do some kind of convert on either? getdate() returns a datetime and LastRun is a datetime so why would this not work?

Thanks for replies,
Naoise

 

[JamesLean]

Can you give an example of two datetimes that you think are giving an incorrect result?

--James

 

[Naoise]

I insert the data into the LastRun field via an asp page using Now(), this has inserted the value 2005-07-06 12:05:57.000. When I run this SQL query

select getdate() as Now, convert(datetime,date_time,121) as LastRun, datediff("minute",convert(datetime,LastRun,121),getdate()) as "Diff" from TableA

it gives me this result set...

Now LastRun Diff
2005-06-07 14:48:00.750 | 2005-07-06 12:05:57.000 | -41597

Basically the query is just trying to see the number of minutes between the last run date and the current date. I am using convert on LastRun to change it from yyyy-dd-mm to yyyy-mm-dd that getdate seems to return so that I can compare them.

Any ideas?

Thanks,
Naoise

 

[PHV]

And what about simply this ?
select getdate() as Now, date_time as LastRun, datediff("minute",date_time,getdate()) as "Diff" from TableA
Should returns 163 minutes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

I had that originally but it was returning a large minus number like before, that is why I thought that I had to convert the date_time field into the same format as what getdate() returns. When I run the SQL you gave above I get -41579

 

[Naoise]

-41579 in minutes is about 28 days which looks like the difference between the formats of getdate() and the date_time field is in one is yyyy-mm-dd, the other yyyy-dd-mm. How and ever my convert is still not giving me any results.

 

[PHV]

In fact I believe that the culprit is the asp page inserting a wrong DateTime value ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

I don't understand, what is wrong with the asp date time value? How do I change it, getdate() returns in odbc canonical format. Is the asp Now() command supposed to insert in that format?

 

[PHV]

Just to be sure, what is the result of this ?
SELECT Month(date_time) FROM TableA

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

It returns 7 for July, so how do I change the output of Now() in asp, currently it has the output 07/06/2005 15:45:41 which is correct, but when it gets entered into the database it is assuming a different format moving the day into the month. What would be the best way of fixing this? I have tried convert in my sql query.

 

[JamesLean]

When you build your query in ASP, rather than concatenate the value of Now(), which leaves you open to these kinds of interpretation problems, just use GETDATE() and let SQL Server handle the date.

--James

 

[PHV]

The ANSI way is: 'yyyy-mm-dd hh:mm:ss'
So, if your asp is VBScript, you can try something like this:
Year(Now) & "-" & Right("0" & Month(Now),2) & "-" & Right("0" & Day(Now),2) & " " & _
Right("0" & Hour(Now),2) & ":" & Right("0" & Minute(Now),2) & ":" & Right("0" & Second(Now),2)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Thanks to all, I will probably just lash getdate into my sql statement for the insert but I will stick the ANSI way into a faq for myself just in case I have to format Now() in the future. Thanks for replies