More date issues -- sql server data mining

Adam Machanic
4/20/2004 1:22:14 PM

Have you tried CASTing to smalldatetime and then using DATEDIFF?

DATEDIFF(dd, CAST(date_field1 AS smalldatetime), CAST(date_field2 AS
smalldatetime))

Tell your db designer to use proper datetime datatypes to avoid integrity
problems, performance issues, and kludge workarounds like this one.

[quoted text, click to view]

Eleonora
4/20/2004 2:29:38 PM

Hello there,

Our db designer is storing dates (date_field) as nvarchar (yyyymmdd) and
not as smalldate.

So I usually need to use the syntax RIGHT() to display the dates in an user
reading friendly format such as dd-mm-yy and the CAST(date_field AS int) to
order and search for them.

However, in a VIEW, I cannot seem to find a way to calculate the days
between 2 dates as we would using DATEDIFF. I tried (CAST(date_field2 AS
int) - CAST(date_field1 AS int)) but I get invalid results.

Can someone please help me out?

TIA

Eleanor

sql server data mining : More date issues