I’ve got a small project at work that required me to use the date, but not the time in a datetime field for retrieval of certain rows from a table in SQL Server 2000. A bit of Googling revealed a document titled “Remove seconds from datetime SQL“. The title isn’t quite right, as the author’s query removes hours and minutes too, but the result turned out to be just what I wanted.
The query: SELECT Cast(Convert(varchar, GetDate(),105) as datetime)
I ended up using code 101 instead of 105 to get my query working, but that was it.
Wow. I’ve been doing this with UDFs, which are performance costly and never knew this existed. Thanks!
thank you so much…. saved me a ton of time…
Another option is to use:
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
This just adds back the number of days since the minimum date, to the minimum date which effectively eliminates the time portion of the datetime.
..or
select cast(floor(cast(getdate() as float)) as datetime)
Can anyone give an example of the linq to sql equivalent of these SQL expressions? In other words: What is the best way to remove the time from a datatime field in linq to sql??
If you’re using LINQ to SQL, you can use the .Date property of a DateTime object to get the date portion.
db.Records.Where(row => row.EventDate.Date == dateFilter)
I may not be the first to ask this question: Why doesn’t SQL have a function to do this automatically?
SQL 2008 does have a new data type called date only,
it does require you to remove the time part thogh before inserting innto it.
ie if using .net you would need to use .toshortdate.tostring