Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert datetime field without millisecond

Author  Topic 

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-13 : 10:15:55
Hello all,
in the Datetime field fo my table, I have this datetime value (for example):

2012-12-13 16:05:20.850

and in my C# code I got this value without millisecond:

2012-12-13 16:05:21

Now I have to write a query that use this value to get my record, but if I write:

2012-12-13 16:05:21 = 2012-12-13 16:05:20.850

I got nothing (obviously).

How can I solve this problem?

Luigi

sanjnep
Posting Yak Master

191 Posts

Posted - 2012-12-13 : 11:06:13
May be you can do something like this

compare after casting to date

CAST('2012-12-13 16:05:20.850' as DATE) = CAST('2012-12-13 16:05:21' as DATE)

Thanks,
Sanjeev
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-13 : 11:06:40
where dte >= @d and dte < dateadd(ss,1,@d)

You could do an operation on the date to get rid of the milliseconds but that would mean it couldn't us an index.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-13 : 11:07:55
Compare to
DATEADD(ms,-DATEPART(ms,YourValue),YourValue)


Edit: Wow! twice!! Ouch!!!
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-12-13 : 11:16:13
Well done, thank you all.

Luigi
Go to Top of Page
   

- Advertisement -