Include milliseconds in time literal in SQL?

21 Nov 2005 9:11 AM

Armin Zingler

Hi,

in a datetime field in a database, the date/time value is
11/21/2005 09:30:45.753. The interesting part is the seconds. It's 45.753
seconds. How do I query for this exact value? Maximum resolution seems to be
seconds: "...where value=#21/11/2005 09:30:45#"
This is not allowed: "...where value=#21/11/2005 09:30:45.753#
This not, too: "...where value=#21/11/2005 09:30:45:753#

So, how do I include the milliseconds in a time literal in an SQL?

Armin

21 Nov 2005 1:04 PM

Miha Markic [MVP C#]

Use parameters instead (SqlParameter).

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"Armin Zingler" <az.nospam@freenet.de> wrote in message
news:%23uUsBvn7FHA.808@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> in a datetime field in a database, the date/time value is
> 11/21/2005 09:30:45.753. The interesting part is the seconds. It's 45.753
> seconds. How do I query for this exact value? Maximum resolution seems to
> be
> seconds: "...where value=#21/11/2005 09:30:45#"
> This is not allowed: "...where value=#21/11/2005 09:30:45.753#
> This not, too: "...where value=#21/11/2005 09:30:45:753#
>
> So, how do I include the milliseconds in a time literal in an SQL?
>
>
> Armin
>

21 Nov 2005 1:32 PM

Armin Zingler

"Miha Markic [MVP C#]" <miha at rthand com> schrieb
> Use parameters instead (SqlParameter).

Possible, but I'm interested in how to do it on my own.

Armin

21 Nov 2005 1:06 PM

Cowboy (Gregory A. Beamer)

SELECT * FROM TABLE
WHERE VALUE='2005-11-21 07:05:07.810'

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

Show quote

"Armin Zingler" <az.nospam@freenet.de> wrote in message
news:%23uUsBvn7FHA.808@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> in a datetime field in a database, the date/time value is
> 11/21/2005 09:30:45.753. The interesting part is the seconds. It's 45.753
> seconds. How do I query for this exact value? Maximum resolution seems to
> be
> seconds: "...where value=#21/11/2005 09:30:45#"
> This is not allowed: "...where value=#21/11/2005 09:30:45.753#
> This not, too: "...where value=#21/11/2005 09:30:45:753#
>
> So, how do I include the milliseconds in a time literal in an SQL?
>
>
> Armin
>

21 Nov 2005 1:30 PM

Armin Zingler

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> schrieb
> SELECT * FROM TABLE
> WHERE VALUE='2005-11-21 07:05:07.810'

This is not a valid date/time format. Date/time literals must be enclosed in
'#'. The ''' indicates a string, thus I get a data type mismatch error.

Armin

21 Nov 2005 1:56 PM

Cor Ligthert [MVP]

Armin,

You know that your litterals are wrong, you use European standard, the
literarls are in the current versions still all based on USA notation.

(and therefore I don't like them, gives me to much confusion)

Just to bring your attention on.

Cor

21 Nov 2005 2:09 PM

Armin Zingler

"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> schrieb
> Armin,
>
> You know that your litterals are wrong, you use European standard,
> the literarls are in the current versions still all based on USA
> notation.
>
> (and therefore I don't like them, gives me to much confusion)
>
> Just to bring your attention on.

You're right. I mistyped in the posting only. Must have been
11/21/2005 of course. Thanks!

Armin