asked on Convert string to date
Hi,
I NEED HELP PLEASE! :-)
I have an excel sheet that has a data from a vendor.
One of the field that I need from the excel sheet is the Submit_Date.
they have it as string nvarchar "February 12, 2015 2:07:21 PM EST"
I have created a table with that field as datetime.
the problem that I'm having when I do my import from excel to SQL. it import the Submit_Date as string from excel
but my table " ClearQuestReport" has Submit_Date as datetime .
when I run my stored Procedure I get this error
ERROR:
Msg 241, Level 16, State 1, Procedure CQ_ClearQuestReport, Line 24
Conversion failed when converting date and/or time from character string.
This is my stored Procedure.
what I need is to convert Submit_Date field from string to datetime field when I import the data into my table "ClearQuestReport"
Thank you
I NEED HELP PLEASE! :-)
I have an excel sheet that has a data from a vendor.
One of the field that I need from the excel sheet is the Submit_Date.
they have it as string nvarchar "February 12, 2015 2:07:21 PM EST"
I have created a table with that field as datetime.
the problem that I'm having when I do my import from excel to SQL. it import the Submit_Date as string from excel
but my table " ClearQuestReport" has Submit_Date as datetime .
when I run my stored Procedure I get this error
ERROR:
Msg 241, Level 16, State 1, Procedure CQ_ClearQuestReport, Line 24
Conversion failed when converting date and/or time from character string.
This is my stored Procedure.
what I need is to convert Submit_Date field from string to datetime field when I import the data into my table "ClearQuestReport"
UPDATE ClearQuestReport
SET
id = ClearQuestReport_Import.id,
Headline = ClearQuestReport_Import.Headline,
Description = ClearQuestReport_Import.Description,
Submited_By = ClearQuestReport_Import.[Submited By],
Submit_Date = ClearQuestReport_Import.Submit_Date
FROM ClearQuestReport
INNER JOIN ClearQuestReport_Import ON ClearQuestReport.id = ClearQuestReport_Import.id
--This will insert all non matching records
INSERT INTO ClearQuestReport(id,Headline,Description,Submited_By,Submit_Date)
SELECT id,Headline,Description,[Submited By],Submit_Date
FROM ClearQuestReport_Import
WHERE
NOT EXISTS (SELECT 1
FROM ClearQuestReport
WHERE ClearQuestReport.Id = ClearQuestReport_Import.Id)
SELECT * FROM ClearQuestReport
Thank you
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
lulu50
ASKER
Jim,
I don't have an access to the vendor.
I need to a way to convert it in my procedure while importing the data.
I don't have an access to the vendor.
I need to a way to convert it in my procedure while importing the data.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Hi ,
you can use REPLACE to avoid timezone like:
SELECT CAST( REPLACE('February 12, 2015 2:07:21 PM EST','EST','') AS datetime) AS Submit_Date
is return
2015-02-12 14:07:21.000
in your SP you can use Submit_Date instead of 'February 12, 2015 2:07:21 PM EST'
Best of Luck,,,
you can use REPLACE to avoid timezone like:
SELECT CAST( REPLACE('February 12, 2015 2:07:21 PM EST','EST','') AS datetime) AS Submit_Date
is return
2015-02-12 14:07:21.000
in your SP you can use Submit_Date instead of 'February 12, 2015 2:07:21 PM EST'
Best of Luck,,,
ASKER
Thank you
Part of the deal-i-o here is that when Excel opens it will enforce formatting which changes the presentation of the values in a way that causes it to not load into a datetime field correctly.