x

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
more ▼

asked Feb 18, 2011 at 12:05 PM in Default

dabeacon gravatar image

dabeacon
11 1 1 1

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.
Feb 19, 2011 at 02:09 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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/][2]. Even if you decide to solve your particlular problem using SET DATEFORMAT, you should still read Jeff Modens article.

[2]: http://www.sqlservercentral.com/articles/T-SQL/62867/
more ▼

answered Feb 18, 2011 at 12:23 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

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]
more ▼

answered Feb 19, 2011 at 02:15 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x96
x34
x22

asked: Feb 18, 2011 at 12:05 PM

Seen: 2877 times

Last Updated: Feb 18, 2011 at 12:05 PM