I am trying to convert a NVCHAR datatype to a DATETIME data type is that possible?Only if it's a valid date. Post a simple example of failure along with associated code and data values and let's see what we can do.|||DECLARE @.x TABLE (Col1 nvarchar(25))
INSERT INTO @.x(Col1)
SELECT '12/31/2004 11:59:59' UNION ALL
SELECT 0 UNION ALL
SELECT 'Happy New Year'
SELECT CONVERT(datetime,Col1) FROM @.x WHERE ISDATE(Col1) = 1|||Here is the select statement I am using but when I use it it doesnt just give me the 2004 only it gives me everything in the table
SELECT TM#, LastName, FirstName, [Date]
FROM Revocations_Tbl
WHERE ([Date] BETWEEN '01/01/2004' AND '12/31/2004')
this is what the tables design looks like all the other tables have DATETIME for their datatypes and I have no problems, when I tried to convert the datatype I got alot of error messages|||To be honest...I'd fix the table...
ALSO! Anyone figure out why I'm getting the conversion error in the following...
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myRevocations_Tbl99(TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
GO
INSERT INTO myRevocations_Tbl99 (TM#, LastName, FirstName, [Date])
SELECT 1, 'Kaiser', 'Brett', '10/24/2004' UNION ALL
SELECT 2, 'Kaiser', 'Brett', '01/01/2005' UNION ALL
SELECT 3, 'Kaiser', 'Brett', '12/31/2003' UNION ALL
SELECT 4, 'Desiree', 'A', '01/01/2004' UNION ALL
SELECT 5, 'Desiree', 'Whos', '10/19/2004' UNION ALL
SELECT 6, 'Desiree', 'Your', '10/19/200a' UNION ALL
SELECT 7, 'Desiree', 'Their', '12/31/2004' UNION ALL
SELECT 8, 'A Row', 'Of Data 1', 'aaaaaaaaaa' UNION ALL
SELECT 9, 'A Row', 'Of Data 2', '0' UNION ALL
SELECT 0, 'A Row', 'Of Data 3', '123456789'
GO
-- Your's
DECLARE @.x varchar(10), @.y varchar(10)
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE [Date] BETWEEN @.x AND @.y
GO
-- Mine
DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM (SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1) AS XXX
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO
SET NOCOUNT OFF
DROP TABLE myRevocations_Tbl99
GO|||ALSO! Anyone figure out why I'm getting the conversion error in the following...
It looks like the optimizer is flattening out your query into a single table scan, eliminating the ISDATE, then choking on the strings that aren't dates. At least that's what the estimated plan indicates.
Seems like an odd thing to do.|||Put the row for [TM#] 8 first, then it will work.
-PatP|||Put the row for [TM#] 8 first, then it will work.
-PatP
Like I'd have any control over the data...|||Well actually Pat, It blows up right away...|||This works...
DECLARE @.xxx table (TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
INSERT INTO @.xxx (TM#, LastName, FirstName, [Date])
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1
DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM @.xxx
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO
