November 16, 2006 at 4:27 am
I have got two varchar string like: 01/01/1994
One of these strings represents date of diagnosis and the other represents the date of birth. How do I subtract one from the other to give me the age when diagnosis was made. I would like to do this in a single SQL statement.
I feel like performing something like:
CAST(CAST(DODiag AS DATE) - CAST(DOB AS DATE)AS YEAR)
But I just know that that would be wrong. Does anyone please have any suggestions?
November 16, 2006 at 4:38 am
Declare @birth datetime,
@diagnost datetime
Select @birth='19500312',
@diagnost='20061101'
Select DateDiff(year,@birth,@diagnost)
November 16, 2006 at 4:53 am
DATEDIFF(YEAR, CAST(DOB AS DATETIME), CAST(DODiag AS DATETIME) )
Caused the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
November 16, 2006 at 5:19 am
You need to use CONVERT instead of CAST.
The style will be 101 for US dates or 103 for UK dates.
Ideally you should alter the table so that dates are stored as either datetime or smalldatetime.
It is not a good idea to store dates in an ambiguous format
-- =========
-- Test Data
DECLARE @t TABLE
(
DOB char(10) NOT NULL
,DODiag char(10) NOT NULL
)
INSERT INTO @t
SELECT '01/01/1994', '10/03/2005' UNION ALL
SELECT '10/02/1989', '03/09/2006' UNION ALL
SELECT '12/11/2005', '11/12/2006'
-- End of Test Data
-- =========
-- The query
SELECT
-- This just subtracts the year part.
DATEDIFF(
year
, CONVERT(datetime, DOB, 101)
, CONVERT(datetime, DODiag, 101) )
-- Need this test in case birthday has not been reached
- CASE
WHEN DATEPART(dy, CONVERT(datetime, DODiag, 101))
>= DATEPART(dy, CONVERT(datetime, DOB, 101))
THEN 0
ELSE 1
END AS ageATDiag
FROM @t
[Edit] Sorry, the above is slightly wrong due to leap years! Use:
SELECT
-- This just subtracts the year part.
DATEDIFF(
year
, CONVERT(datetime, DOB, 101)
, CONVERT(datetime, DODiag, 101) )
-- Need this test in case birthday has not been reached
- CASE
WHEN MONTH(CONVERT(datetime, DODiag, 101)) >= MONTH(CONVERT(datetime, DOB, 101))
AND DAY(CONVERT(datetime, DODiag, 101)) >= DAY(CONVERT(datetime, DOB, 101))
THEN 0
ELSE 1
END AS ageATDiag
FROM @t
November 16, 2006 at 7:49 am
This appears to work nicely. Thanks. <strike> I have an unrelated task. That is the removal of full stops in all strings of a field. For example, the string 167.4 would need to become 1674. Is there a substitute function in T-SQL?</strike> (question posted elsewhere)
November 20, 2006 at 2:23 am
Lookup REPLACE in BOL, essentially it's
SELECT
[field 1],
REPLACE ([yourfield here], '.', '')
FROM
table
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply