CODEWITHSUNDEEP
phpsqldatabasecodeigniter
kev_m
kev_m

Reputation: 325

Selecting a date range using month and year code igniter

i want to have this function where i can select all records from database by inputting only a month and current year

my database date format is 'YYYY-MM-DD'

and for example i select june for month and enter, it will search june1-june30 of the current year.

Upvotes: 0

Views: 1875

Answers (4)

kev_m
kev_m

Reputation: 325

i have no clear answer recieved so i just put up all the ideas and i came up with something like this,

"SELECT * FROM attendance WHERE month(DATE) = ".$month." AND year(DATE) = YEAR(now())

where month input is a number from the month e.g 1 - jan, 2 - feb, etc.

Upvotes: 1

ɐlǝx
ɐlǝx

Reputation: 1394

SELECT * FROM TABLE WHERE MONTH(date_field) = MonthVariable AND YEAR(date_fied) = YEAR(GETDATE())

GETDATE() return current day and so year and
assuming that the variable destined to month (MonthVariable) contains a number reported per month

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

Here would be my solution. You can leave the Year as NULL but you then have the option to select the year should you want to.

Test Data

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (ID int, DateField datetime)
INSERT INTO #TestData (ID, DateField)
VALUES
(1,'2016-01-01')
,(2,'2016-02-02')
,(3,'2016-01-25')
,(4,'2016-02-16')
,(5,'2015-01-12')
,(6,'2015-01-20')

Query

DECLARE @MonthVariable int; SET @MonthVariable = '1'
DECLARE @YearVariable int; SET @YearVariable = '2016'

SELECT
ID
,Datefield
FROM #TestData
WHERE DATEPART(mm,DateField) = @MonthVariable
AND (DATEPART(yy,DateField) = @YearVariable
    OR @YearVariable IS NULL)

Output

ID  Datefield
1   2016-01-01 00:00:00.000
3   2016-01-25 00:00:00.000

If you will ALWAYS want the current year then you can do this;

DECLARE @MonthVariable int; SET @MonthVariable = '1'

SELECT
ID
,Datefield
FROM #TestData
WHERE DATEPART(mm,DateField) = @MonthVariable
AND DATEPART(yy,DateField) = DATEPART(yy,GETDATE())

Upvotes: 0

Nilan
Nilan

Reputation: 94

You can try building a query for the below

select * from thetable where monthname(date_field) = 'February'

or month(), if you want to return a number

select * from thetable where month(date_field) = 2

Upvotes: 0

Related Questions