user3005015 user3005015 - 3 months ago 9
SQL Question

Selection on single date fails, between dates works fine?

in an application I am working on , I need to be able to select records from a single date, or a selection of records of that chosen date, plus x minus y days ( a range of dates)

this is the working, multiple dates sql:

ALTER PROCEDURE [dbo].[sp_Select_Scandagen]

@datvoor datetime,
@datna datetime,
@scanner varchar(30)
AS
BEGIN

select ScannerID,ScannedTime,ScannedCode from [dbo].[tblScannedItems]
WHERE ScannedTime BETWEEN @datvoor AND @datna and ScannerID like @scanner;


this is the non-working single date sql:

ALTER PROCEDURE [dbo].[sp_Select_Scandag]

@datum datetime,
@scanner varchar(30)
AS
BEGIN

select ScannerID,ScannedTime,ScannedCode from [dbo].[tblScannedItems]
WHERE ScannedTime like @datum AND ScannerID like @scanner;


When I execute the second one in SSMS I get:

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_Select_Scandag]
@datum = N'15/7/2016',
@scanner = N'975001'

SELECT 'Return Value' = @return_value

GO
Msg 8114, Level 16, State 5, Procedure sp_Select_Scandag, Line 2
Error converting data type nvarchar to datetime.


Any suggestions?

thx,
James

Answer

Sql sever throws Error converting data type nvarchar to datetime. because N'15/7/2016' is a language dependent date format. Use language independent date format

@datum = N'20160715' 

You can check your locale settings with dbcc useroptions; command.

And if you need to compare only date part of Datetime variables cast both to Date.

WHERE cast(ScannedTime as date) = cast(@datum as date) AND ... 
Comments