CODEWITHSUNDEEP
sql-server
Arya Alfy
Arya Alfy

Reputation: 5

How to Select between two periode of month and year eg: january 2018 - january 2019?

SQL query problem

select *
from app_contract_hd
where year(created_datetime) between 2018 and 2018 and MONTH(created_datetime)between 4 and 4

Upvotes: 0

Views: 148

Answers (6)

Arulkumar
Arulkumar

Reputation: 13237

One possible way using CONCAT and checking with BETWEEN

SELECT *
FROM app_contract_hd
WHERE CONCAT(YEAR(created_datetime), MONTH (created_datetime)) BETWEEN 201801 AND 201901

Upvotes: 0

pankaj singh
pankaj singh

Reputation: 105

you could try this

SELECT * FROM APP_CONTRACT_HD

WHERE 
(YEAR(CREATED_DATETIME) >=2018 AND MONTH(CREATED_DATETIME)>=1) 

OR
(YEAR(CREATED_DATETIME) <=2019 AND MONTH(CREATED_DATETIME)<=1)

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

If your 'created_datetime' column is 'datetime' data type, you may try the next approach using DATEFROMPARTS() function:

SELECT *
FROM app_contract_hd
WHERE CONVERT(date, created_datetime) BETWEEN DATEFROMPARTS(2018, 1, 1) AND DATEFROMPARTS(2019, 1, 1)

Additionally, if your want to filter your data with specific hour, use DATETIMEFROMPARTS():

SELECT *
FROM app_contract_hd
WHERE created_datetime BETWEEN DATEFROMPARTS(2018, 1, 1, 0, 0, 0, 0) AND DATEFROMPARTS(2019, 1, 1, 12, 0, 0, 0, 0)

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

As you have TIME in your created_datetime field, its better if you check the upper range less than +1 day with your original upper range date

SELECT *
FROM app_contract_hd
WHERE created_datetime >= '20180101'
AND created_datetime < '20180201'

Upvotes: 0

Ryan Nghiem
Ryan Nghiem

Reputation: 2438

You can try simple code:

select *
from app_contract_hd
where '2018-01-01' <= created_datetime and created_datetime < '2019-02-01'

Upvotes: 0

Ricardo
Ricardo

Reputation: 111

if the data type is Datetime 

select * from app_contract_hd
where year(created_datetime) between cast('2018-01-01 00:00:00' as datetime) and  cast('2019-01-31 23:59:59' 
as datetime)

Upvotes: 0

Related Questions