CODEWITHSUNDEEP
c#.netdatetimesqlcommand
Bizhan
Bizhan

Reputation: 17085

compare DateTime in SqlCommand

I have a simple SqlCommand in which I want to return all records within a specified DateTime range. (both Date and Time are involved)

var dataReader = new SqlCommand(
    @"Select RecordID from RecordTable
    where RecordTable.WorkingDT between '"+ _startDt +"' and '"+ _endDt +"'",
    _sqlConnection).ExecuteReader();

how do I have to set the values for _startDt and _endDt?

Upvotes: 1

Views: 1941

Answers (3)

Philip Daniels
Philip Daniels

Reputation: 1014

@kmatyaszek, While commonly used, the "yyyy-mm-dd HH:mm:ss" date format is not guaranteed to be unambiguously parsed by SQL server. If you must create SQL from concatenating strings (not necessary in this case as René has shown) then you should use the ISO8601 format, which is just the same but with a T in the middle: "yyyy-mm-ddTHH:mm:ss".

http://msdn.microsoft.com/en-us/library/ms190977%28v=sql.90%29.aspx

"The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings."

For a demonstration why, try this rerunnable Sql script.

if object_id('tempdb..#Foo') is not null drop table #Foo;
create table #Foo(id int, d datetime)

-- Intend dates to be 12th Jan.
set dateformat ymd
insert into #Foo(id, d) values (1, '2012-01-12 01:23:45')   -- ok
insert into #Foo(id, d) values (2, '2012-01-12T01:23:45')   -- ok
set dateformat ydm
insert into #Foo(id, d) values (3, '2012-01-12 01:23:45')   -- wrong!
insert into #Foo(id, d) values (4, '2012-01-12T01:23:45')   -- ok

select * from #Foo order by id

if object_id('tempdb..#Foo') is not null drop table #Foo;

Upvotes: 1

René Wolferink
René Wolferink

Reputation: 3548

If you add them as proper parameters in your command, you don't need to worry about formatting. The added benefit of getting into the habit of using these is that you don't have to worry about SQL injection when you were to supply strings as parameters.

Have a look at http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06

It shows how to use parameters in your queries. I don't think it needs to be spelled out completely.

An example (copy/pasted from the site):

// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
    "select * from Customers where city = @City", conn);

// 2. define parameters used in command object
SqlParameter param  = new SqlParameter();
param.ParameterName = "@City";
param.Value         = inputCity;

// 3. add new parameter to command object
cmd.Parameters.Add(param);

// get data stream
reader = cmd.ExecuteReader();

And yes, defining the parameters can be done shorter that 3 lines per parameter. But that's left up to the reader.

Upvotes: 2

kmatyaszek
kmatyaszek

Reputation: 19296

You can try this:

var dataReader = new SqlCommand(
    @"Select RecordID from RecordTable
    where RecordTable.WorkingDT between '"+ _startDt.ToString("yyyy-MM-dd HH:mm:ss") +"' and '"+ _endDt.ToString("yyyy-MM-dd HH:mm:ss") +"'",
    _sqlConnection).ExecuteReader();

Where _startDt and _endDt are type of DateTime.

Upvotes: 2

Related Questions