convert nchar(20) field to a datetime data type in sql server 2005

Question

I need to select from a table and filter on dates greater than a specified date.

The problem I'm having is that the dates are stored as nchar(20) and I can't seem to get it converted to date in the where clause.

SELECT CONVERT(DATETIME,log_time,20) from dbo.logs 
where CONVERT(DATETIME,log_time,20) > '10/20/2008'

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string.

Answer 1

try this:

select convert(datetime, cast(trim(log_time), nvarchar)) from dbo.logs
where convert(datetime, cast(trim(log_time), nvarchar)) > '10/20/2008'

I think the trick is that you need to trim the string and cast to nvarchar

Answer 2

Msg 195, Level 15, State 10, Line 1 'trim' is not a recognized built-in function name.

Answer 3

This should work.

select convert(datetime, cast(rtrim(log_time), nvarchar)) from dbo.logs
where convert(datetime, cast(rtrim(log_time), nvarchar)) > '10/20/2008'

Answer 4

Msg 1035, Level 15, State 10, Line 1 Incorrect syntax near 'cast', expected 'AS'.

Answer 5

I talked to our SQL Server DBA. He said the problem was with bad data. He said on some the dates the m was left off of the am or pm. He wrote me a view where he handles the problem for me and now I can select from the view and the date is a datetime data type also.

Thanks for everyone's help.

Billy

Answer 6

Try

select convert(datetime, cast(rtrim(log_time) AS nvarchar)) from dbo.logswhere convert(datetime, cast(rtrim(log_time) AS nvarchar)) > '10/20/2008'

or simply

select CAST(log_time AS DATETIME) from dbo.logs where CAST(log_time AS DATETIME) > '10/20/2008'