How can I select a row having a BigInt and a DateTime field starting with a specigic value?

Question

I am working on a Microsoft SQL Server database and I have the following problem to implement these 2 simple select query.

So I have a table named MyTable that has a column OtherFieldFK of type BigInt with a value of 70016592107.

So for example I want search all the record in this table that have the OtherFieldFK starting with the value 70.

I tried to use like in this way:

select * 
from MyTable 
where OtherFieldFK like = '70%'

but it doesn't work. I think that like clause works only on string, is it right?

In this table I also have a DATETIME column named DataInizioGestione.

I tried to do the same thing:

select * 
from DataInizioGestione
where OtherFieldFK like = '2016%'

but in this case it doesn't work either.

How can I correctly implement these 2 queries?

Answer 1

the first should be right, as you wrote:

select * from MyTable where OtherFieldFK like = '70%'

for the second should be converted to the date format in the nvarchar (es 121 with this format aaaa-mm-gg hh:mi:ss.mmm(24h)); in this way you can make the comparison with the like:

select * from MyTable where convert(nvarchar,DataInizioGestione,121) like = '2016%'

or you can directly compare the year:

select * from MyTable where year(DataInizioGestione) = 2016