|
|
|||||||||
|
|||||||||
|
|||||||||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
Generating a Numerical number for Date Difference
I am trying to calculate the difference between two dates, however I cannot seem to get a viable response when subtracting the two numbers.
The SQL is: Select p.Name , p.ProjectStatus , (Select u.UDFDate AS GoLive FROM UDFdate as u WHERE u.Itemname = 'ProjectText9' AND u.EntityID = CAST(p.ProjectID AS uniqueidentifier)) AS GoLive , (Select u.UDFDate AS SOW_GoLive FROM UDFdate as u WHERE u.Itemname = 'ProjectText19' AND u.EntityID = CAST(p.ProjectID AS uniqueidentifier)) AS SOW_GoLive , ((Select u.UDFDate AS GoLive FROM UDFdate as u WHERE u.Itemname = 'ProjectText9' AND u.EntityID = CAST(p.ProjectID AS uniqueidentifier)) - (Select u.UDFDate AS SOW_GoLive FROM UDFdate as u WHERE u.Itemname = 'ProjectText19' AND u.EntityID = CAST(p.ProjectID AS uniqueidentifier))) AS DIFF FROM Project as p INNER JOIN UDFdate as u on p.ProjectID = u.EntityID WHERE p.Deleted=0 AND p.ProjectStatus = 'A' AND NOT((Select u.UDFDate AS SOW_GoLive FROM UDFdate as u WHERE u.Itemname = 'ProjectText19' AND u.EntityID = CAST(p.ProjectID AS uniqueidentifier)) is NULL) GROUP BY p.Name, p.ProjectID, p.ProjectStatus The Column DIFF shows up as YYYY-MM-DD HH:MM:SS.000 Any suggestions how I can convert the DIFF to be a ##? |
|
#2
|
|||
|
|||
|
You should be able to use DateDiff.
.... ,DateDiff("d", (SELECT u.UDFDate AS golive ....), (SELECT u.UDFDate AS SOW_GoLive ...)) AS DIFF .... Here's a link to info on DateDiff http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx |
![]() |
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Generating a Numerical number for Date Difference |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|