How can I extract dates from a table when the dates in the column contain a forward slash in the field? In my SQL table there is a column called TerminationDate that is set as a (nchar(20)) and the dates contained in that column are in the format 01/01/2011. I am trying to extract dates that are newer than 01/01/2011 but I get "Syntax error converting datetime from character string."Thanks, Steve
asked Feb 18, 2011 at 12:05 PM in Default
You either want to look at SET DATEFORMAT (http://msdn.microsoft.com/en-us/library/ms189491.aspx) or a Split-function.
The later is best implemented using a Tally table (like so many other tasks). Jeff Moden has a great article about usage of a Tally table: [http://www.sqlservercentral.com/articles/T-SQL/62867/]. Even if you decide to solve your particlular problem using SET DATEFORMAT, you should still read Jeff Modens article.: http://www.sqlservercentral.com/articles/T-SQL/62867/
answered Feb 18, 2011 at 12:23 PM
I think its a data issue rather than a TSQL issue. Run this code and then use it to check if all rows are/are not valid as dates in your table:
DROP TABLE [dbo].[TestDates]
answered Feb 19, 2011 at 02:15 AM