Need a date function for current month

My table returns the date in a year/month format of 201104. Is there a criteria that will return current month. The problem I am having is each month I have to go into my queries and change it (ie 201104 to 201105) How about one for previous month? Thanks for the help
SELECT dbo_Monthly_Sales_Tb.Store_Number, dbo_Monthly_Sales_Tb.Operations_Month, dbo_Monthly_Sales_Tb.Net_Sales, dbo_Monthly_Sales_Tb.Vehicle_Count
FROM dbo_Monthly_Sales_Tb
WHERE (((dbo_Monthly_Sales_Tb.Operations_Month)=201104));

Open in new window

Cole100IT Systems ManagerAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
> How about one for previous month?

SELECT
  dbo_Monthly_Sales_Tb.Store_Number,
  dbo_Monthly_Sales_Tb.Operations_Month,
  dbo_Monthly_Sales_Tb.Net_Sales,
  dbo_Monthly_Sales_Tb.Vehicle_Count
FROM
  dbo_Monthly_Sales_Tb
WHERE
  dbo_Monthly_Sales_Tb.Operations_Month = Year(DateAdd("m", -1, Date())) * 100 + Month(DateAdd("m", -1, Date()));

/gustav
0
 
sdstuberCommented:
current month =   format(now,"yyyymm")
0
 
Dale FyeCommented:
if this field [Operations_Month] is numeric field?

WHERE dbo_Monthly_Sales_Tb.Operations_Month = val(format(Date(), "yyyymm"))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:

Depending on what you actually store in your Operations_Month field, you can use

dbo_Monthly_Sales_Tb.Operations_Month) = CONVERT(varchar(6), DATEADD(MM, -1, GETDATE(), 112)
0
 
Ephraim WangoyaCommented:
this way

dbo_Monthly_Sales_Tb.Operations_Month = CONVERT(varchar(6), DATEADD(MM, -1, GETDATE()), 112)
0
 
danishaniCommented:
Try to use below statement;

Year(Date()&Format(Month(Date(),"mm")
0
 
James MurrellProduct SpecialistCommented:
For last month you can use GETDATE and DATEADD as well:

SELECT dbo_Monthly_Sales_Tb.Store_Number, dbo_Monthly_Sales_Tb.Operations_Month, dbo_Monthly_Sales_Tb.Net_Sales, dbo_Monthly_Sales_Tb.Vehicle_Count
FROM dbo_Monthly_Sales_Tb where DATEPART(month, dbo_Monthly_Sales_Tb.Operations_Month)
= (DATEPART(month, GETDATE()) - 1) and
DATEPART(year, dbo_Monthly_Sales_Tb.Operations_Month) = DATEPART(year, DATEADD(m, -1, GETDATE()))
0
 
Dale FyeCommented:
Cole100,

If, as I suspect, these are linked tables and you want to run the query in Access, using the Format() function should work for you.  If, however, you want to run this as a pass-through query, then ewangoya's CONVERT( ) function will perform basically the same function but on the SQL SERVER.
0
 
Cole100IT Systems ManagerAuthor Commented:
SDSTUBER: worked perfectly, thanks

EWANGOYA: I am getting "Undefined expression 'CONVERT' in expression"
SELECT dbo_Monthly_Sales_Tb.Store_Number, dbo_Monthly_Sales_Tb.Operations_Month, dbo_Monthly_Sales_Tb.Net_Sales, dbo_Monthly_Sales_Tb.Vehicle_Count
FROM dbo_Monthly_Sales_Tb
WHERE (((dbo_Monthly_Sales_Tb.Operations_Month)=CONVERT(varchar(6),DateAdd("mm",-1,GETDATE()),112)));

Open in new window

0
 
Helen FeddemaCommented:
If you need the month number, you can use Month(dteTest).  It takes a month value as the argument.  What data type is the Operations_Month field?  Also, do you need to include the year?
0
 
Helen FeddemaCommented:
Convert is not an Access function, in any case.  
0
 
Cole100IT Systems ManagerAuthor Commented:
data type for the field is a number. 6 digit format 'yyyymm' I am trying to do this in access so that explains why convert does not work. i would like to see it exactly how it is stores in the table (yyyymm) thanks
0
 
Anthony PerkinsCommented:
>>Zones: Microsoft Access Database, MS SQL Server, SQL Query Syntax
...
I am trying to do this in access so that explains why convert does not work. <<
Try and refrain from posting in MS SQL Server, it is not appropriate.  The SQL syntax is different.
0
 
OP_ZaharinCommented:
- did you get it to work in Access already? this is another approach in Access where i concatenate the current year and current month. i use format '00' for the month in making sure it formated as 05:

SELECT dbo_Monthly_Sales_Tb.Store_Number, dbo_Monthly_Sales_Tb.Operations_Month, dbo_Monthly_Sales_Tb.Net_Sales, dbo_Monthly_Sales_Tb.Vehicle_Count
FROM dbo_Monthly_Sales_Tb
WHERE dbo_Monthly_Sales_Tb.Operations_Month = (Year(Now()) & Format(Month(Now()),'00')) 

Open in new window

0
 
Gustav BrockCIOCommented:
> data type for the field is a number

If so, this is how to do:

SELECT
  dbo_Monthly_Sales_Tb.Store_Number,
  dbo_Monthly_Sales_Tb.Operations_Month,
  dbo_Monthly_Sales_Tb.Net_Sales,
  dbo_Monthly_Sales_Tb.Vehicle_Count
FROM
  dbo_Monthly_Sales_Tb
WHERE
  dbo_Monthly_Sales_Tb.Operations_Month = Val(Format(Date(), "yyyymm"));

or, if you prefer pure nums:

WHERE
  dbo_Monthly_Sales_Tb.Operations_Month = Year(Date()) * 100 + Month(Date());


/gustav
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT dbo_Monthly_Sales_Tb.Store_Number, dbo_Monthly_Sales_Tb.Operations_Month, dbo_Monthly_Sales_Tb.Net_Sales, dbo_Monthly_Sales_Tb.Vehicle_Count
FROM dbo_Monthly_Sales_Tb
WHERE (((dbo_Monthly_Sales_Tb.Operations_Month)= MONTH(GETDATE()) ));
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT dbo_Monthly_Sales_Tb.Store_Number, dbo_Monthly_Sales_Tb.Operations_Month, dbo_Monthly_Sales_Tb.Net_Sales, dbo_Monthly_Sales_Tb.Vehicle_Count
FROM dbo_Monthly_Sales_Tb
WHERE (((dbo_Monthly_Sales_Tb.Operations_Month)= convert(varchar,year(GETDATE()))+ right('0',convert(varchar,MONTH(GETDATE())),2) ));
0
 
sdstuberCommented:
previous month, same as first post but with DATEADD function as shown above in other posts

previous month =   format(dateadd("mm", -1,now),"yyyymm")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.