Converting data type

Question

I am trying to assign concatenated string to my variable and I am getting the error "Error converting data type varchar to datetime."

Please Can you help me solve this problem.

declare @msg Varchar(max)
set @msg = 'select' + 'File Date: ' + CAST(GetDate() AS VARCHAR(Max))  + ' data imported successfully'


EXEC DataImport
@PackageName = '',
@Status= 'Successful', 
@Message = @msg,
@FileName = 'test',
@PackageExecutionDate = 'test',
@Step = 'Import Flat Files'

I removed the incompatible database tags. Your code does not make sense in either SQLite or MySQL. — Gordon Linoff
– Gordon Linoff, Commented Jul 27, 2020 at 14:05
There's no way a date string will grow beyond 4GB, so there's no reason to use varchar(max). If you want control over the date string you should either use CONVERT with a style or FORMAT with a format string. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 29, 2020 at 10:32

Gordon Linoff · Accepted Answer · 2020-07-27 14:05:51Z

1

You can use CONCAT(), which converts values automatically:

set @msg = concat('select', 'File Date: ', CAST(GetDate() AS VARCHAR(Max)), ' data imported successfully')

Neither of your original database tags support getdate() or varchar(max), so you probably have other problems as well.

answered Jul 27, 2020 at 14:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M-Fulu Over a year ago

Hi @GordonLinoff, I have tried the CONCAT() it also did not work.

M-Fulu · Accepted Answer · 2020-07-29 10:23:59Z

0

I have solved this problem by using C# code instead on converting directly on the SSIS Package using SQL. Thank you.

answered Jul 29, 2020 at 10:23

M-Fulu

1011 silver badge11 bronze badges

Collectives™ on Stack Overflow

Converting data type

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related