Problem in Dynamic Query

Question

0.00/5 (No votes)

See more:

Hi everybody,
Select Query is not returning any values
QUERY:

SQL

SELECT 
    Latitude,
    Longitude 
FROM 
    table 
WHERE 
    Device_DateTime BETWEEN '2014-03-26 14:18:00.000' AND '2014-03-26 14:19:00.000';

-- The above Query is returning values
But in case of Dyanamic Query:

SQL

SELECT @SQL=N'SELECT Latitude,Longitude FROM dbo.' + @tablename +
' WHERE Device_DateTime BETWEEN ''' + @MinId  + ''' AND ''' + @toDateTime + '''
EXEC sp_executesql @SQL

--The above Query is returning Error like Conversion failed when converting date and/or time from character string.

So i changed Query as CAST(Device_DateTime as NVARCHAR(50))

Its not giving any error but values are empty..

Any help can be appreciated...

Posted 26-Mar-14 20:54pm

Member 10017719

Updated 26-Mar-14 22:34pm

Aarti Meswania

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raul Iloc · Accepted Answer · 2014-03-26T21:05:00

I had the same problem in some old projects. The error could be generated if you are using different Culture in the data that come from your application and your DB server. For example if in your code your Thread.CurrentThread.CurrentUICulture.Name == "de-DE" and you are using sending a date like "23/07/2013" (the German format for date is "dd/mm/yyyy"), and your DB server are using English culture ==> an error will be generated because he expect the next date format "mm/dd/yyyy"!

So the solution is that in your code, before to setup the date param to convert it to string by using the CultureInfo used in your DB server.

Schatak · Accepted Answer · 2014-03-26T21:56:00

Solution 2

here is the alternate way, hope this will help without using culture Info

SQL

DECLARE @SFromDate  varchar(20)
DECLARE @SToDate    varchar(20)

SET @SFromDate = convert(char(18),  '2014-03-26 14:18:00.000', 107)
SET @SToDate = convert(char(18), '2014-03-26 14:18:00.000', 107)

DECLARE @SQL varchar(1000)
SELECT @SQL =N'SELECT Latitude,Longitude FROM urtablename
WHERE urDates between CAST(''' + @SFromDate + '''As datetime) and CAST(''' + @SToDate + ''' As datetime)'

exec (@sql)

Posted 26-Mar-14 21:56pm

Schatak

Comments

Member 10017719 27-Mar-14 4:47am

Awesome...:)

Raul Iloc 27-Mar-14 5:19am

Your solution is not solving the problem, and is working only if you know the format of the date time string, but this was the problem that this format depends on the current culture used in the application that call the stored procedure.