query column of string type with the date restrain in sqlserver.txt

I have a sql table whose create sql is shown as following:

++++++++++ Create table script ++++++++

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
    [LogTime] [nvarchar](50) NULL,
    [EventTime] [nvarchar](50) NULL,
    [value] [nvarchar](50) NULL,
) ON [PRIMARY]
GO

//+++++++++ End ++++++++

However when I select the data from the table,this is the value.

LogTime                         EventTime            value
--------------------------------------------------------------------
2010-11-26 12:10:31.907000000   634263264000000000   1.145604E-10
2010-11-26 12:10:31.907000000   634263264000000000   1.373898E-9
2010-11-26 12:10:31.907000000   634263264000000000   -2.3787419E-10

Why the Eventtime is not the date format?

Also,I need to select something from the table according a date period,for example get dat whose eventtime between "2010-10-12 12:00:00" and "2010-10-12 17:00:00",so how to build the sql words?

BWT,I can not change the structure of the table,and I just need to read data from the db,never write.

**

UPDATE:

**

One of the sql date format is :

YYYY-MM-DD HH:MI:SS.MMM(24h)    ODBC Canonical (with milliseconds)  SELECT CONVERT(VARCHAR(23), GETDATE(), 121)     1972-02-19 06:35:24.489

But why the milliseconds digit of date in my table is nine?

Like:

1972-02-19 06:35:24.489

my data: 2010-11-26 12:1开发者_StackOverflow社区0:31.907000000

I want to remove the milliseconds,so how to convert it in the sql word?

---

Based on the current values that you have shown for EventTime, i think you need to clarify from whoever is responsible for populating this table (as you have indicated that it is not your code) what format is the data in EventTime column.

As it currently is, i am not aware of any particular SQL date time format that can represent this information as a date for you to be able to do the processing of the WHERE condition that you want.

List of common SQL Date formats

Additionally, i would suggest that if possible, post the code that is responsible for populating the Testtable as that might give some indicator as to the data (and its format) in EventTime column.

Edit: Based on the comments, it seems that the data is stored as a proper date (albeit in string format) in the database. If that is the case, the below should work.

Note: 121 is the format that includes milliseconds as well. You can optionally use 120 as well if your dates do not need milliseconds

SELECT LogTime, CONVERT(DateTime, eventTime, 121), value 
FROM Test
WHERE CONVERT(DateTime, eventTime, 121) BETWEEN 
    CONVERT(DateTime, '2010-10-12 12:00:00', 121) AND 
    CONVERT(DateTime, '2010-10-12 17:00:00', 121) 

Note : You could potentially avoid the convert for each row on the 2 hardcoded values (possibly params) by doing it just once and storing it in a datetime variable instead. Also, as per the definition, BETWEEN is inclusive of both boundaries.

---

if you can, change eventTime to the datetime data type instead of varchar