CODEWITHSUNDEEP
c#t-sqldatetimesql-server-2008-r2
Paul Stanley
Paul Stanley

Reputation: 2161

Select datetime column from SQL Server 2008 R2 table

I have a DateTime column on a table in SQL Server 2008 R2 database. My c# front end is inserting a row in this table with a datetime parameter.

DateTime T = DateTime.Now;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "cargridsnapshot";
cmd.Parameters.AddWithValue("@t", T);
cmd.ExecuteNonQuery();

The datetime column in the row in the table is this: 

2013-09-04 16:21:23.450

but 

select * from table 
where TIMECOLUMN = '2013-09-04 16:21:23.450'

returns no results.

Upvotes: 1

Views: 1055

Answers (4)

user240141
user240141

Reputation:

What if you use this

Select * from table where convert(date,yourcolumn)=convert(date,your_datetime)

but kindly note it will compare on date only

Upvotes: 1

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

SQL Server can represent datetime in a variety of ways when comparing a string to your database value. Some have the month before the day, some day before month. The localization of your SQL Server is causing your issue; you can read more about it here. Specifically, look for SET DATEFORMAT.

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

SQL Server only stores time to approximately 1/300th of a second-you are probably using a value that cannot accurately be represented with the available bits.

Edit

You can check this behavior with the following code:

select CONVERT(DATETIME, '2013-09-04 16:21:23.450') -- returns 2013-09-04 16:21:23.450
select CONVERT(DATETIME, '2013-09-04 16:21:23.451') -- returns 2013-09-04 16:21:23.450
select CONVERT(DATETIME, '2013-09-04 16:21:23.452') -- returns 2013-09-04 16:21:23.453
select CONVERT(DATETIME, '2013-09-04 16:21:23.453') -- returns 2013-09-04 16:21:23.453
select CONVERT(DATETIME, '2013-09-04 16:21:23.454') -- returns 2013-09-04 16:21:23.453
select CONVERT(DATETIME, '2013-09-04 16:21:23.455') -- returns 2013-09-04 16:21:23.457

Bottom line

Don't try to identify rows with a datetime...

Upvotes: 1

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241525

The SQL Server datetime type is accurate only only to increments of .000, .003, or .007 seconds.

So when you save a value with .450, that will be fine. But if you saved with .451, that would get rounded back down to .450, and then it wouldn't match up.

You can avoid this by using a datetime2 column type.

Also - you probably shouldn't be storing DateTime.Now in your database. Read here.

Upvotes: 0

Related Questions