Geek asks and answers

Conversion error while converting date and time from character string in storage procedure

I get a conversion error when starting below sql in the store procedure, because for some reason I cannot use in varchar

Declare @sql varchar(100)
Declare @ddtime datetime

set @ddtime = '2012-02-03 22:14:50.057'

set @sql = 'select * from table1 where tdate='+@ddtime

exec(@sql)
+5
source share
3 answers

You need to convert @ddtimeto varcharsince you include it in the string:

Declare @sql varchar(100)
Declare @ddtime datetime

set @ddtime = '2012-02-03 22:14:50.057'

set @sql =  'select * from table1 where tdate= '''+ convert(nvarchar(25), @ddtime, 121) + ''''

exec(@sql)

or just you @ddtimeas varcharinstead datetime.

Declare @ddtime varchar(25)

convert(varchar(25), @ddtime, 121) leaves your date in the same format in which you originally set it.

How to format date and time on Sql server

+10
source

Change the data type of the @ddtime variable to varchar. You are trying to combine varchar and datetime.

+1
source

ddtime datetime

.

set @sql = 'select * from table1 where tdate='''+cast ( @ddtime as nvarchar(100))+''''


Declare @ddtime nvarchar(100)
0
source

More articles:


All Articles