SQL Server Performance Forum – Threads Archive
Date without time
Hi. Simple question (SQL Server 2005): Is it possible to make a user-defined data type derived from datetime that does not store the timepart? If so, how? Thanks,EBYou don’t need a user-defined type for this. See if this helps:http://www.sql-server-performance.com/fk_datetime.asp —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
I know I can use something like this SELECT *
FROM tbl
WHERE CONVERT(CHAR(10), date1, 104) = CONVERT(CHAR(10), date1, 104) wich involves using two function calls. Other solutions would be to create a trigger on the table that removes the timepart before storing to a datetime type. Also creating a CLR type in Visual Studio I guess would solve the issue, but this will probably be the slowest solution. So that’s why I thought of making a user-defined data type derived from datetime. Thanks,EB
"Other solutions would be to create a trigger on the table that removes the timepart before storing to a datetime type."
Why don’t you remove the time when storing to table ? rather than to have it remove using trigger Have you check out that link ? Removing the time can be easily done with
select dateadd(day, datediff(day, 0, getdate()), 0)
KH
]]>