SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert datetime field without millisecond		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/13/2012 :  10:15:55  Show Profile  Reply with Quote
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

USA
190 Posts
Posted - 12/13/2012 :  11:06:13  Show Profile  Visit sanjnep's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 12/13/2012 :  11:06:40  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/13/2012 :  11:07:55  Show Profile  Reply with Quote
Compare to 
DATEADD(ms,-DATEPART(ms,YourValue),YourValue)


Edit: Wow! twice!! Ouch!!!
Edited by - sunitabeck on 12/13/2012 11:08:49
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 12/13/2012 :  11:16:13  Show Profile  Reply with Quote
Well done, thank you all.

Luigi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000