Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need a date function for current month

Posted on 2011-05-03
18
Medium Priority
?
531 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Cole100
  • 2
  • 2
  • 2
  • +8
18 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 248 total points
ID: 35515440
current month =   format(now,"yyyymm")
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35515445
if this field [Operations_Month] is numeric field?

WHERE dbo_Monthly_Sales_Tb.Operations_Month = val(format(Date(), "yyyymm"))
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35515450

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35515459
this way

dbo_Monthly_Sales_Tb.Operations_Month = CONVERT(varchar(6), DATEADD(MM, -1, GETDATE()), 112)
0
 
LVL 12

Expert Comment

by:danishani
ID: 35515461
Try to use below statement;

Year(Date()&Format(Month(Date(),"mm")
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 35515493
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35515516
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
 

Author Comment

by:Cole100
ID: 35515725
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35515866
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35515877
Convert is not an Access function, in any case.  
0
 

Author Comment

by:Cole100
ID: 35515937
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35579451
>>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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35586960
- 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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35662825
> 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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35689047
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35689051
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 252 total points
ID: 35689158
> 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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35689278
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question