CODEWITHSUNDEEP
sql-servert-sql
cesco
cesco

Reputation: 93

Conversion failed when converting date and/or time from char string

I received a file and I'm trying to clean the data by inserting this previous table into a new table. Most of the columns should have 'Y' or 'N' but some cells are empty and I'm giving them a value of N. When running the queries, I get Conversion failed when converting date and/or time from character string. Last activity has date set up this way: 8/23/2018 8:44:00 PM

This is the table that I have: Email_Roster:

 [ID_NUM] [INT] IDENTITY(1,1) NOT NULL,
 [key] [VARCHAR](50) NOT NULL,
 [2017_SL2_s] [VARCHAR](50) NOT NULL,
 [2017_SL2_o] [VARCHAR](50) NOT NULL,
 [2017_SL2_c] [VARCHAR](50) NOT NULL,
 [2017_SL2_b] [VARCHAR](50) NOT NULL,
 [2017_SL2_u] [VARCHAR](50) NOT NULL,
 [last_activity_date] [datetime] NULL,
 [last_email] [VARCHAR](50) NULL,

This is the query that I wrote to replace the empty cells but get an error of failed when converting date and/or time from character string.

INSERT INTO [dbo].[training] 
     (
       [2017_SL2_s]
      ,[2017_SL2_o]
      ,[2017_SL2_c]
      ,[2017_SL2_b]                        
      ,[2017_SL2_u]
      ,[last_activity_date]
      ,[last_email_date]
       )

    SELECT
        ISNULL ([2017_SL2_s], 'N') [2017_SL2_s],
        ISNULL ([2017_SL2_o], 'N') [2017_SL2_o],
        ISNULL ([2017_SL2_c], 'N') [2017_SL2_c],
        ISNULL ([2017_SL2_b], 'N') [2017_SL2_b],
        ISNULL ([2017_SL2_u], 'N')[2017_SL2_u],
        [last_activity_date], 
         [last_email]
        FROM [dbo].[Email_Roster]

Upvotes: 0

Views: 114

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

The date string you seem to be receiving is not ANSI compliant so you are going to have to use convert and pass it a date style. https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

Something like this.

select convert(date, '8/23/2018 8:44:00 PM', 110)

Upvotes: 2

Related Questions