Select Records by Month in SQL Server – How to Guide

datesql server

I want to get the records for a month of January'2017 and here is the one row sample of my huge table

Table:

CUSTTRIPID  TPTRIPID    CUSTOMERID  ENTRY_TOLLTXNID EXIT_TOLLTXNID  ENTRYTRIPDATETIME   EXITTRIPDATETIME    TRIPIDENTMETHOD TRIPCHARGETYPE  ENTRYLANEID ENTRYPLAZAID    EXITLANEID  EXITPLAZAID VEHICLENUMBER   VEHICLESTATE    VEHICLECLASS    VEHICLEID   TAGREFID    TOLLAMOUNT  FEEAMOUNTS  DISCOUNTSAMOUNT OUTSTANDINGAMOUNT   TRIPSTAGEID TRIPSTATUSID    TRIPSTATUSDATE  POSTEDDATE  PAYMENTSTATUSID CREATEDDATE CREATEDUSER UPDATEDDATE UPDATEDUSER REFLINKTRIPID   PLAZA
39038181    34040337    20272679    35126165    35126165    2017-05-04 08:15:54.000 2017-05-04 08:15:54.000 980 P   NULL    NULL    NULL    NULL    KA03D8435   KA  VC10    272614  34161FA8203289720249F4C0    30.00   0.00    0.00    30.00   3   1   2017-05-04 08:31:54.657 2017-05-04 08:31:54.657 456 2017-05-04 08:32:11.130 TRIPPOST    2017-05-04 08:32:11.130 TRIPPOST    0   BangaloreNelamangalaTollPlaza

I have applied this query to get the Date part because the requirement was to retrieve CUSTOMERID more than 2 records with time (of a month).

Query:

SELECT   TOP (10) [CUSTOMERID], 
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS] 
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME] 
HAVING   COUNT(*) > 2;

Result:

CUSTOMERID  DATEPART    TIMEPART
20010438    12/16/2016  22:16:55
20015278    1/28/2017   5:18:20
20108092    2/10/2017   0:37:09
20018833    1/27/2017   4:53:32
20199820    1/27/2017   5:34:24
20124122    2/1/2017    2:47:01
20240710    3/21/2017   13:02:18
20143691    3/21/2017   8:08:03
20074212    12/15/2016  7:28:56
20114518    12/18/2016  6:11:31
20009754    12/17/2016  20:38:40
20072106    3/17/2017   19:37:20
20092287    1/28/2017   10:54:06
20055028    1/27/2017   4:36:51
20003676    4/23/2017   1:34:11

Now how to get the result for a month, assume for January 2017 ?

Best Answer

SELECT DATEPART(MONTH, your_date_field) FROM your_table

SELECT DATEPART(YEAR, your_date_field) FROM your_table

SELECT my_field_1, my_field_2,,,,  
FROM my_table(s)  
WHERE   
DATEPART(YEAR, your_date_field) = 2017  
AND   
DATEPART(MONTH, your_date_field) = 1

An example can be found in the fiddle here (month) and here (year)!