Dilip Dilip - 1 year ago 119
SQL Question

Convert a sysdate() into int datatype

I have a sysdate() value - 2016.10.18. I need a SQL to convert it to int in MS SQL SERVER.
INPUT - 2016.10.18
OUTPUT reqd. - 20161018

I tried this - SELECT CONVERT(int, '2016.10.18') but it throws the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2016.10.18' to data type int.

Also tried this - select CONVERT(int,CONVERT(decimal(19,2),'2016.10.18'))

Getting the following error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Could someone help me out!?

Answer Source
Select Convert(int,Convert(varchar,cast('2016.10.18' as date),112))



Either of the following will work. If you have a use the replace() method, you will need to strip the time portion off via LEFT(...,10)

Select Convert(int,Convert(varchar,cast('2016.10.18 14:24:49' as date),112))
Select cast(Replace(Left('2016.10.18 14:24:49',10),'.','') as int)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download