Using SQL Server 2008, how would you convert [datediff(day, dtcreated,getdate()) AS Age] into its equivalent if "dtcreated" is defined as varchar(10)?

Hi Zimmer9,

Below is working for me , You dont have to do anything? Do you need something else?

SELECT datediff(day, dtcreated,getdate()) AS Age FROM tableName should work fine.

DECLARE @DATE AS VARCHAR(10) = '04/12/1982'

SELECT DATEDIFF(DAY, @DATE, GETDATE()) AS Age

SELECT DATEDIFF(YEAR, @DATE, GETDATE()) AS Age

Select allOpen in new window

Output

/*------------------------
DECLARE @DATE AS VARCHAR(10) = '04/12/1982'

SELECT DATEDIFF(DAY, @DATE, GETDATE()) AS Age

SELECT DATEDIFF(YEAR, @DATE, GETDATE()) AS Age
------------------------*/
Age
-----------
12701

(1 row(s) affected)

Age
-----------
35

(1 row(s) affected)

Select allOpen in new window

Hope it helps!

Guess I just needed some reassurance. Thanks Pawan.

Also note that it is recommended to use DATE or DATETIME datatype to stored dates in the SQL Server to avoid conversion at the run time. Sometimes when you use the conversion in the where clause and in the join condition it may slow down the performance of the query.  Since the optimizer chooses to go with the full scan rather than using the index if there.

Glad to help Zimmer9 !!

Since whether it works on not depends on server datefirst and/or language settings, I would explicitly CONVERT the varchar value to a date:

SELECT DATEDIFF(DAY, CONVERT(date, '01/18/1987', 101), GETDATE()) AS Age_in_Days /*should always work*/

Just in case, because:

SELECT DATEDIFF(DAY, '01/18/1987', GETDATE()) AS Age_in_Days /*works fine under U.S. date/language settings*/
SET LANGUAGE German
SELECT DATEDIFF(DAY, '01/18/1987', GETDATE()) AS Age_in_Days /*oops!*/

Thanks Scott. I greatly appreciate it.  :)

I strongly suggests that we should always use proper data types other wise we will be on trouble. See below-

SELECT DATEDIFF(DAY, CONVERT(DATE, '01/18/87', 101), GETDATE()) AS Age_in_Days /*WILL NOT work*/

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT ISDATE('01/18/87') - now it will give me 1 means valid date. And since you have Varchar data type the database will accept it and your query will break in future. See in the above case even it is valid date code break.

It will any way break for other than Date.

Note - In this case any setting or conversion will not help.

Hope it helps!

If it's possible that the century was not stored -- though that was never mentioned in the original q -- then of course you would have to adjust the conversion code accordingly:

SELECT dtcreated, DATEDIFF(DAY, CONVERT(DATE, dtcreated,
    CASE WHEN CHARINDEX('/', REVERSE(dtcreated)) = 5 THEN 101 ELSE 1 END), GETDATE()) AS Age_in_Days
FROM (
    VALUES('01/18/1987'),('01/18/87'),('5/5/2012'),('5/5/12')
) AS test_date_strings(dtcreated)

Yes Scott thats what I wanted to mention - In this case we will have to play around the query so that we can handle the data and thats not a good way to handle it.

Today we might able to write a query but if tomorrow somebody adds a junk in the table our code will break.

 Its just not the right choice of data type at the time of designing the table.  

Since the author said he has dates thats why I haven't added conversion since the SQL will do that automatically. I hope going forward the author will consider this.

No, Pawan, you don't, unless you have some evidence that the century would be mysteriously removed from some rows.  Easy enough to correct that even if it is somehow happening:
1) add a constraint to prevent it
2) add a trigger to add the century to any date in which it's missing.

My company can't be the only one dealing with more and more international issues.  A specific format should be used when the date format is known and could be misinterpreted due solely to server settings.

Outstanding points Scott. Thanks for taking the time.  :)

Well I am not 100% with Scott on this as this all depends. Any way Zimmer9, Do you need any more help with this question?

Regards,
Pawan

No, I'm good. Thanks gentlemen.