How to use Where clause with datetime in SQL Server 2008

Question

I have written this query:

SELECT * 
FROM w_tbl 
WHERE DID NOT IN (SELECT DId FROM tbl_Assign) 
  AND CAST(MeasDateTime AS Date) = @dta 
ORDER BY 
    CAST(MeasDateTime as DATE) DESC

So as you can see I am sending a parametrized query.

So I put 24/09/2010 00:00:00 for @dta

But I get this error:

Conversion failed when converting date and/or time from charterer string.

I tell you why I am doing this and my purpose:

I have a datetime column, and I want to select/fetch all data by date only (i.e. I don't want to fetch datetime wise, it has to be just according to date)

Do you mean you're setting the value of `@dta` to a *string*? Why not set it to a `DateTime` instead? — Jon Skeet, May 06 '13 at 06:41
I would suspect that MeasDateTime can have not valid value. What is the type for this column by the way? — , May 06 '13 at 06:44
@JonSkeet My field in DB is also datetime, its not string.... — Jitendra Kumar Rajbhar, May 06 '13 at 06:44
hi jitendar, can you first check @dta value coz 24/09/2010 may be wrong date. try using 09/24/2010 and check if it works. — Vishal Vaishya, May 06 '13 at 06:53
@JitendraKumarRajbhar: You seem to have confused everyone - where is your `@dta` value coming from? If it's *not* coming from .NET code, why is .net in your tag list? — Jon Skeet, May 06 '13 at 06:56
@VishalVaishya I was actully missing this logic. Thanks for the help. Could you please post it in answer so that i can mark in as correct answer. Regards. — Jitendra Kumar Rajbhar, May 06 '13 at 07:03

score 0 · Answer 1 · answered May 06 '13 at 06:48

If you want to select by date only and you have DateTime type, use date functions:

DATEADD ( dd , 0 , DATEDIFF ( dd , 0 , @dta))

So in your case:

SELECT * 
FROM w_tbl 
WHERE DID NOT IN (SELECT DId FROM tbl_Assign) 
  AND DATEADD ( dd , 0 , DATEDIFF ( dd , 0 , MeasDateTime)) = DATEADD ( dd , 0 , DATEDIFF ( dd , 0 , @dta)) 
ORDER BY 
    MeasDateTime DESC -- No need for CAST/CONVERT here, it will be ok either way

score -1 · Answer 2 · answered May 06 '13 at 07:20

-1

First check @dta value cause 24/09/2010 may be wrong date. Try using 09/24/2010 or 24-Sep-2010 and check. Always to avoid confusion in dd/MM/yyyy and MM/dd/yyyy use MMM for month. SQL handles datetime format with MMM correctly. Like dd-MMM-yyyy. By default SQL uses US standards and take date is format MM/dd/yyyy.

Thanks.

answered May 06 '13 at 07:20

Vishal Vaishya

596
3
15

The only format that SQL Server interprets consistently is `YYYYMMDD`; this is [documented](http://msdn.microsoft.com/en-us/library/ms187819.aspx) and using other formats for date literals will break sooner or later due to locale settings. – Pondlife May 06 '13 at 14:47
@Pondlife. Thanks for updating with documentation. But for above question Jitendra got confused with dd/MM/yyyy and MM/dd/yyyy date formats which was his actual problem which I want to highlight Jitendra and others while using locale formats for casting or converting. – Vishal Vaishya May 07 '13 at 09:04

How to use Where clause with datetime in SQL Server 2008

2 Answers2