July 24, 2015 at 12:37 pm
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013'
"05/01/2014"...etc...my requirement is to convert this column into Datetime..any suggestions?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
July 24, 2015 at 12:46 pm
sree25 (7/24/2015)
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this"04/24/2013'
"05/01/2014"...etc...my requirement is to convert this column into Datetime..any suggestions?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
Quick suggestion, have a look at this.
😎
July 24, 2015 at 12:54 pm
First, the correct syntax for CAST is CAST(<expression> AS <datatype>).
Second, saying it didn't work doesn't give us much information to go on. Exactly how didn't it work? Did it produce an error? If so, what was the error? Or did it produce results, but not the results you were expecting?
Also, it's possible that some of your records may not be in the format you expect. Instead of using CAST() try TRY_PARSE(<expression> AS <datatype>) instead. That will return dates when possible and a NULL value otherwise.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2015 at 12:56 pm
What do you mean by didn't work?
Are you trying to change the column definition or just use it as datetime in a query?
July 24, 2015 at 1:05 pm
Does this gives you an idea on what to do?
CREATE TABLE dbo.SampleDates(DateofRecord nvarchar(20));
INSERT INTO dbo.SampleDates
VALUES
('"04/24/2013'''),
('"05/01/2014"')
--Option 1
--Just the Query
SELECT convert(datetime,replace(replace(DateofRecord,'"',''),'''',''))
FROM dbo.SampleDates
--Option 2
--Create new column with correct data type, assign correct values, drop original column, rename new column
ALTER TABLE dbo.SampleDates ADD DateofRecordNew datetime;
UPDATE dbo.SampleDates
SET DateofRecordNew = convert(datetime,replace(replace(DateofRecord,'"',''),'''',''))
ALTER TABLE dbo.SampleDates DROP COLUMN DateofRecord;
EXEC sp_rename 'dbo.SampleDates.DateofRecordNew', 'DateofRecord', 'COLUMN';
SELECT * FROM dbo.SampleDates
GO
DROP TABLE SampleDates
GO
CREATE TABLE dbo.SampleDates(DateofRecord nvarchar(20));
INSERT INTO dbo.SampleDates
VALUES
('"04/24/2013'''),
('"05/01/2014"')
--Option 3
--Clean data on the column, alter the data type.
UPDATE dbo.SampleDates
SET DateofRecord = replace(replace(DateofRecord,'"',''),'''','')
ALTER TABLE dbo.SampleDates ALTER COLUMN DateofRecord datetime;
SELECT * FROM dbo.SampleDates
GO
DROP TABLE SampleDates
July 24, 2015 at 2:05 pm
Actually it is a view..and it is defined as below
SELECT
QUOTENAME ('Call_Date', '"') as Call_Date
, QUOTENAME ('Acquisition_Date', '"') as Acquisition_Date
, QUOTENAME ('DateofRecord', '"') as DateofRecord
UNION ALL
SELECT
QUOTENAME (convert(varchar(11), Loan.MaturityDate, 101), '"') as Call_Date
, QUOTENAME (convert(varchar(11), Loan.AcquiredDate, 101), '"') as Acquisition_Date
, QUOTENAME (convert(varchar(11), Loan.DateofRecord, 101), '"') as DateofRecord
FROM HISTORICAL.Loan Loan
and we have to use this query in SSRS report with Dateofrecord as Parameter..with datet type...so for that i need to convert this column into datetime....
and i am trying to query like this in ssrs report
select
Call_Date,Acquisition_Date,DateofRecord from <viewname> where convert(datetime,replace(DateofRecord,'"',''''),101)=@DateofRecord
then i am getting below error
"Conversion failed when converting date and/or time from character string"
July 24, 2015 at 2:13 pm
It may be that some of the values in your table do not represent valid dates.
If you try to convert to datetime and any row has a bad value in the column to be converted, you will get an error.
declare @dates table (date1 nvarchar(50))
insert into @dates
values
('04/24/2013')
,('05/01/2014')
-- ,('13/13/2015')-- uncomment this to force error
select CONVERT(datetime,date1), CONVERT(date,date1)
from @dates
You can use the TRY_CONVERT() function to identify the values that might be giving you trouble.
select TRY_CONVERT(datetime,date1) as converted_value, date1 as original_value
from @dates
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 24, 2015 at 2:19 pm
Just saw your last post. Why on earth are you trying to put text for column headers at the front of your actual data?
Of course "Call Date", "Acquisition_Date" and "DateofRecord" won't covert to valid datetimes.
You CANNOT mix datatypes within a column like that. Take the first half of the union out. Your report should provide the column headings. They should not be included with the data presented.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 24, 2015 at 2:28 pm
good catch....got it...that is report requirement...we need to send as it is to our users..now i will filter that one record..
July 24, 2015 at 2:45 pm
There are better ways to include column names with output. I don't know of any other system that handles column names the way that view does. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply