November 6, 2009 at 2:09 am
Hi All,
I have a string in the following format which I am trying to convert to SQL Server datetime.
yyyymmdd24miss
Eg. 20091102165659
I have tried casting using the following statement:
Select cast (20091102165659 as datetime)
Error received - Arithmetic overflow error converting expression to data type datetime.
I have also tried using a 'Data Conversion' transformation with Data Type; 'database timestamp [dt_DBTIMESTAMP]
Error received - [Data conversion [638]] Error: Data conversion failed while converting column "timestamp" (163) to column "timestamp" (655). The conversion returned status value 2 and status text "The value could not be converted because of a potentail loss of data.
The destination field in SQL Server is set to standard datetime.
Any assistance would be really appreciated.
Regards,
Chris
November 6, 2009 at 3:05 am
declare @date varchar(50)
declare @converted_date datetime
set @date = '20091102165659'
set @converted_date = (select substring(@date,0,5)+'/'+ substring(@date,5,2)+'/'+ substring(@date,7,2)+' '+ substring(@date,9,2)+':'+
substring(@date,11,2)+':'+substring(@date,13,2))
print @converted_date
try this.Not the efficient one. But last option to do it.
November 6, 2009 at 3:40 am
Hi Churi,
Many thanks for your suggestion, this works in SQL Server, but how would I implement within the SSIS data flow task.
Many Thanks,
Chris
November 6, 2009 at 3:09 pm
I solve this problem with the following CAST statement:
Select Cast(Stuff(Stuff(Stuff(Stuff(Stuff('20091102165659', 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':') as DateTime)
Many thanks all for your advice.
Regards,
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply