asked
Forward slash in character string
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
your sample date doesnt illustrate whether the nchar format is dd/mm/yyyy or mm/dd/yyyy, can you please confirm which of these is a something that might be found in your data - 21/02/2011 or 02/22/2011.
You either want to look at SET DATEFORMAT ([
http://msdn.microsoft.com/en-us/library/ms189491.aspx][1]) 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/][2]. Even if you decide to solve your particlular problem using SET DATEFORMAT, you should still read Jeff Modens article. [1]:
http://msdn.microsoft.com/en-us/library/ms189491.aspx [2]:
http://www.sqlservercentral.com/articles/T-SQL/62867/
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: USE [AdventureWorks] GO CREATE TABLE TestDates ( ADate NCHAR(20) ) -- create test table -- create some test values INSERT INTO TestDates VALUES ( '01/01/2011' ), ( '01/12/2011' ), ( '01/22/2011' ), ( '01/21/2011' ), ( '01/16/2011' ), ( '01/30/2011' ), ( '21/01/2011' ) -- convert all values that can be converted to dates SELECT [dbo].[TestDates].[ADate] , CASE WHEN ISDATE(adate) = 1 THEN CONVERT(DATETIME, adate) ELSE ''--adate + 'isnt a date' END AS As_A_Date FROM testdates -- list all values and whether they are or are not convertable SELECT [dbo].[TestDates].[ADate] , CASE WHEN ISDATE(adate) = 1 THEN adate + ' - is a valid date' ELSE adate + '- isnt a valid date' END AS As_A_Date FROM testdates -- remove test table DROP TABLE [dbo].[TestDates]
