By replacement then ...
SELECT CONVERT(DATETIME,GETDATE(),103) -- "(now)"
Ok, let's create a table to match this wishlist of a SELECT ... with datafile(td).txt
Zany, Assignment quisling.tito.com yes 2013-02-04 11:38:01.760
Captain, Theodorus mike.twentysixteen.com yes 2012-12-14 11:59:02.110
Wynn, Cruikshank ss.nautpott.com no 2011-07-26 07:52:15.007
CREATE SCHEMA cpqa
USE [cpqaMM]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblSomeTime]') AND type in (N'U'))
DROP TABLE [cpqa].[tblSomeTime]
GO
CREATE TABLE [cpqaMM].[cpqa].[tblSomeTime](
[name][nvarchar](73),
[email][nvarchar](42),
[fmUser][nvarchar](5),
[sIn][datetime]
)
BULK INSERT [cpqaMM].[cpqa].[tblSomeTime] FROM 'C:\cpqaMM\datafile(td).txt'
SELECT [name]
,[email]
,[fmUser]
,[sIn]
FROM [cpqaMM].[cpqa].[tblSomeTime]
GO
Barest of assignment ... should pull the middle record ...
SELECT * FROM [cpqaMM].[cpqa].[tblSomeTime] WHERE CONVERT(datetime,[sIn],103)>='01/01/2012' AND CONVERT(datetime,[sIn],103)<='12/31/2012'
-- Captain, Theodorus mike.twentysixteen.com yes 2012-12-14 11:59:02.110
So, the ideal situation is above, that's what you want. And this is what you have instead:
USE [cpqaMM]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblBadTime]') AND type in (N'U'))
DROP TABLE [cpqa].[tblBadTime]
GO
CREATE TABLE [cpqaMM].[cpqa].[tblBadTime](
[name][nvarchar](73),
[email][nvarchar](42),
[fmUser][nvarchar](5),
[sIn][nvarchar](101)
)
BULK INSERT [cpqaMM].[cpqa].[tblBadTime] FROM 'C:\cpqaMM\datafile(td).txt'
SELECT [name]
,[email]
,[fmUser]
,[sIn]
FROM [cpqaMM].[cpqa].[tblBadTime]
GO
SELECT * FROM [cpqaMM].[cpqa].[tblBadTime] WHERE CAST(CONVERT(nvarchar,[sIn],103) AS [datetime])>='01/01/2012' AND CAST(CONVERT(nvarchar,[sIn],103) AS [datetime])<='12/31/2012'
Oh, wait a minute ... that's not what I have! I have CAST(CONVERT(datetime,[sIn],103) AS [datetime]) ... which is yielding the tragic:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value