How to use 'like' in a query

Question

How to use 'like' in a query

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
December 24, 2015 at 11:27 am

#303407

I have a table called 'Activity' and there is a column called 'Transaction_Date',
I am trying to filter only the year 2015 activities.
The 'Transaction_Date' output format is like this.....'2015-01-20 01:00:07.573'
How can I filter with 'Like' clause to filter on year 2015.
Regards,
SQLisAwe5oMe.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

WHERE Transaction_Date >= '20150101' AND Transaction_Date < '20160101'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

Thanks Scott.

Sorry, I am really bad at writing queries, trying to learn.

Can you look at the below query and help me.

I need to be able join two tables(Name & Activity) and pull the values based on Activity_Type = 'CALL' and SOURCE_CODE = 'BWILSON' and Transaction_Date = 11/30/2015%.

select Name.ID, Name.FULL_NAME, Name.FULL_ADDRESS, Name.EMAIL, Activity.ID, Activity.ACTIVITY_TYPE, activity.SOURCE_CODE, activity.TRANSACTION_DATE, Activity.DESCRIPTION

from Name

INNER JOIN Activity

ON Name.ID=Activity.ID

order by Name.ID, activity.TRANSACTION_DATE;

Regards,
SQLisAwe5oMe.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

select Name.ID, Name.FULL_NAME, Name.FULL_ADDRESS, Name.EMAIL, Activity.ID, Activity.ACTIVITY_TYPE, activity.SOURCE_CODE, activity.TRANSACTION_DATE, Activity.DESCRIPTION

from Name

INNER JOIN Activity

ON Name.ID=Activity.ID

where Activity.Activity_Type = 'CALL' and SOURCE_CODE = 'BWILSON' and

Activity.Transaction_Date >= '20151130' and

Activity.Transaction_Date < '20151201'

order by Name.ID, activity.TRANSACTION_DATE;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Thanks Scott, it worked.

Appreciate it.

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

Thanks Scott, it worked.

Appreciate it.

Regards,
SQLisAwe5oMe.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

SQLisAwE5OmE (12/24/2015)
Thanks Scott, it worked.
Appreciate it.

Do you understand the code that Scott wrote and why it's better to use the >=/< method he used?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

Yes, it's giving me all activities between 11/30 to 12/1.

Correct?

Regards,
SQLisAwe5oMe.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

SQLisAwE5OmE (12/24/2015)
Yes, it's giving me all activities between 11/30 to 12/1.
Correct?

Technically, not correct especially since "BETWEEN" in T-SQL is inclusive of both end values.

Scott's code giving you all activities from the first instant of 20151130 up to and NOT including the first instant of 20151201. The advantage of doing it that way is that never have to worry about the resolution of the temporal datatype being used.

I realize that the difference is semantically subtle but such an understanding can make a huge difference in whether correct values are returned or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 9

Ok, thanks for explaining.

I'm more of a infrastructure DBA, so getting into more development now.

Anyway, I really appreciate the feedbacks.

Regards,
SQLisAwe5oMe.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 10

Since Transaction_Date contains the time, a query coded like this:

Activity.Transaction_Date BETWEEN '20151130' and '20151201'

Would return rows from 12/01/2015 at exactly midnight (if there were any). But that wouldn't really be valid if you only wanted to see transactions from the day of 11/30/2015.

Since Transaction_Date is a datetime, you might think you should do this:

Activity.Transaction_Date BETWEEN '20151130' and '20151201 23:59:50.997'

but not only do you have to know to use .997 because .999 rounds up to the next day, what happens if you decide in the future to get a more accurate time and you make that column a datetime2? Uh oh, then you're losing any rows from 23:59:59.998 to 23:59:59.999999.

To get around all that, it's much better to code it as we did above:

Activity.Transaction_Date >= '20151130' and Activity.Transaction_Date < '20151201'

Now, no matter what the degree of accuracy of the time in that column, you get only rows for the one day.

Similarly, the best way to get, say, an entire year's worth of data would be like this:

Activity.Transaction_Date >= '20150101' and Activity.Transaction_Date < '20160101'

It seems odd at first, and takes a little getting used to, but it's worth it for consistantly accurate results.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

ScottPletcher (12/25/2015)
Since Transaction_Date contains the time, a query coded like this:
Activity.Transaction_Date BETWEEN '20151130' and '20151201'
Would return rows from 12/01/2015 at exactly midnight (if there were any). But that wouldn't really be valid if you only wanted to see transactions from the day of 11/30/2015.
Since Transaction_Date is a datetime, you might think you should do this:
Activity.Transaction_Date BETWEEN '20151130' and '20151201 23:59:50.997'
but not only do you have to know to use .997 because .999 rounds up to the next day, what happens if you decide in the future to get a more accurate time and you make that column a datetime2? Uh oh, then you're losing any rows from 23:59:59.998 to 23:59:59.999999.
To get around all that, it's much better to code it as we did above:
Activity.Transaction_Date >= '20151130' and Activity.Transaction_Date < '20151201'
Now, no matter what the degree of accuracy of the time in that column, you get only rows for the one day.
Similarly, the best way to get, say, an entire year's worth of data would be like this:
Activity.Transaction_Date >= '20150101' and Activity.Transaction_Date < '20160101'
It seems odd at first, and takes a little getting used to, but it's worth it for consistantly accurate results.

+1000. Could not have said it better myself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 12

Thanks again Scott for explaining.

Regards,
SQLisAwe5oMe.