AndreaNobili AndreaNobili - 5 months ago 8
SQL Question

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

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

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