|
[VIEWED 4621
TIMES]
|
SAVE! for ease of future access.
|
|
|
|
Mukurdhom
Please log in to subscribe to Mukurdhom's postings.
Posted on 12-14-10 4:17
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0 
? 
|
| |
Hello Gurus
I am kind of stuck here calculating the datetime difference.
Here is the problem
Environment: SQL Server 2008
Given: ResponseDate = '2010-10-02 06:05:13.120" ShippedDate='2010-10-03 07:08:25.780
Calculate date Difference = (ShippedDate - ResponseDate) = 1 01:03:12:660 1 = number of days 01 = Hours 03 = Minutes 12=Seconds 660= Milliseconds
My Query
SELECT ResponseDate ,ShippedDate ,DATEDIFF(day, ShippedDate, ResponseDate) AS DateDifference
This query returns only number of days.  Any gurus here in Sajha. Please help me.
|
| |
|
|
|
|
prankster
Please log in to subscribe to prankster's postings.
Posted on 12-14-10 4:28
PM [Snapshot: 16]
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
The argument day is not right
- datepart
-
Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.
datepart |
Abbreviations |
year |
yy, yyyy |
quarter |
qq, q |
month |
mm, m |
dayofyear |
dy, y |
day |
dd, d |
week |
wk, ww |
hour |
hh |
minute |
mi, n |
second |
ss, s |
millisecond |
ms |
microsecond |
mcs |
nanosecond |
ns |
|
| |
|
|
BI-Polar
Please log in to subscribe to BI-Polar's postings.
Posted on 12-14-10 4:33
PM [Snapshot: 24]
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
select top 100 Shippeddate,Responsedate, CAST(DATEDIFF(DD,Shippeddate,Responsedate) AS VARCHAR)+' '+CAST(DATEDIFF(HH,Shippeddate,Responsedate) AS VARCHAR) +':'+CAST(DATEDIFF(MM,Shippeddate,Responsedate) AS VARCHAR)+':'+CAST(DATEDIFF(SS,Shippeddate,Responsedate) AS VARCHAR) +':'+CAST(DATEDIFF(MS,Shippeddate,Responsedate) AS VARCHAR) From YourTabel This might work......
|
| |
|
|
Mukurdhom
Please log in to subscribe to Mukurdhom's postings.
Posted on 12-14-10 4:43
PM [Snapshot: 23]
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
I am using this syntax DATEDIFF ( datepart , startdate , enddate ) DATEDIFF(day, ShippedDate, ResponseDate)
This returns the correct number of days.
If i use other arguments, say Hour 2 days = it's returning 48
I think this cannot be done with a single query. Either a Function() or Stored Procedure.
Have you come across this situation? Or anyone in sajha.
|
| |
|
|
Mukurdhom
Please log in to subscribe to Mukurdhom's postings.
Posted on 12-14-10 4:52
PM [Snapshot: 47]
Reply
[Subscribe]
|
| Login in to Rate this Post:
0
?
|
| |
It worked !!!  @Bi-Polar Thank you very much  @Pransketer Thank you for your help.  Just need some tuning in formating.. I can do that.
Sajha has always been a great help.
Happy Tuesday !!
|
| |
