CODEWITHSUNDEEP
sqlsql-server
anatp_123
anatp_123

Reputation: 1205

How to get results only having month and year for the date?

With the below query i want to be able to get data from a specific month/year that i get from a submitted form. In the form they get to choose two month/year and then user submits form.

Query is working but problem comes in since I do not have the days, and some months have 28/30/31 days.

For example lets say date_one = 2016/01 and date_two=2016/10 i would get error

Conversion failed when converting date and/or time from character string.

since I have no day in the variables. I could be solving this problem the wrong way.

How Can I solve this problem with only having month/year for options?

SELECT .......
from test_table
WHERE  paid_dt between  <cfqueryparam  cfsqltype="cf_sql_integer" value="#date_one#">  and
<cfqueryparam  cfsqltype="cf_sql_integer" value="#date_two#"> 
GROUP BY
    DATEPART(year, paid_dt),datename(month,paid_dt),
    datepart(month, paid_dt)
ORDER BY
DATEPART(year, paid_dt) ASC,
datepart(month, paid_dt) ASC

I have sql server 2012.

Upvotes: 1

Views: 397

Answers (2)

Munavvar
Munavvar

Reputation: 821

Try this query.

I dont know about data in paid_dt. (Assumes it has date in it)

SELECT .......
from test_table
WHERE (YEAR(CONVERT(DATE,paid_dt)) > YEAR(CONVERT(Date,date_one+'/01')) 
        AND
        MONTH(CONVERT(DATE,paid_dt)) > MONTH(CONVERT(Date,date_one+'/01')))
      AND
        (YEAR(CONVERT(DATE,paid_dt)) < YEAR(CONVERT(Date,date_two+'/01')) 
        AND
        MONTH(CONVERT(DATE,paid_dt)) < MONTH(CONVERT(Date,date_two+'/01')))
GROUP BY YEAR(paid_dt), MONTH(paid_dt)
ORDER BY YEAR(paid_dt) ASC, MONTH(paid_dt) ASC

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82020

Your can use try_convert().. For example

Select Try_Convert(Date,'2016/10'+'/01')   -- Notice the addition of /01

Returns

2016-10-01

Invalid conversions will return null.

To get the End of Month

Select EOMonth(Try_Convert(Date,'2016/2'+'/01'))

Returns

2016-02-29

Upvotes: 1

Related Questions