[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MsQuery - Selecting a date that is 1st day of current year  and another date that is 1st day of next year

Posted on 2011-10-25
7
Medium Priority
?
258 Views
Last Modified: 2012-05-12
I currently have a MSQuery that has  '2011-01-01'   as DATEA   and   '2012-01-01'   as DATEB

I would like the formulas  for DATEA and DATEB
using getdate() as today's date that can be used  so

 DATEA will be first day of the current year
and DATEB will be first day of the next year.

0
Comment
Question by:morinia
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:sentner
ID: 37026510
So you just need to extract the year, and have DATEA return 1/1/<current year> and DATEB return 1/1/<current year+1> ?

DATEA would be:  =DATE(YEAR(TODAY()),1,1)
DATEB would be:  =DATE(YEAR(TODAY())+1,1,1)

0
 

Author Comment

by:morinia
ID: 37027531
The above code didn't work in my query,  I am using MSSQL.  The query below is what I need to modify.

Currently, I am getting all enrollment dates within the last year 11.1.2010  -   10.1.2011

I would like 1.1.2010  - 12.1.2010   Do you know how to modify this query to do that?

SELECT member_id, enrollment_date
FROM TABLE
WHERE enrollment_date <=  convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120)
   and enrollment_date > dateadd(year, -1,  convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
0
 
LVL 14

Expert Comment

by:sentner
ID: 37027555
Ah, you posted this in the excel zone and asked about a formula, so I assumed you wanted an excel formula.  Perhaps it should be in the ms sql zone.
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!

 

Author Comment

by:morinia
ID: 37027573
You are correct.  I am running a query from Excel.  Is there a way to transfer to MS SQL?
0
 
LVL 14

Expert Comment

by:sentner
ID: 37027674
I've already opened a request for attention, so a moderator should check on this in a day or so..

However, this shouldn't be that difficult to modify.  You should be able to extract the year portion of the date.  Looking at:
http://www.tizag.com/sqlTutorial/sqldate.php

Looks like you can pull the year with YEAR(getdate())), or perhaps using the datepart function like datepart(year,getdate()).  Then you need to just have to re-format that back as a full date (not sure the function to do that).
0
 
LVL 14

Accepted Solution

by:
sentner earned 2000 total points
ID: 37027697
Here you go.  This guy wrote some functions to deal with dates like this.  You can use them as a reference or just take them verbatim.  There is one called FirstDayOfTheYear and one called LastDayOfTheYear.

http://jacek-szarapa.com/index.php?p=sql&d=39
0
 

Author Closing Comment

by:morinia
ID: 37027965
Thanks.  The final solution

SELECT member_id, enrollment_date
FROM TABLE
WHERE enrollment_date >= DATEADD(yy, DATEDIFF(yy, 0,getdate()), 0)
   and enrollment_date > < DATEADD(yy, DATEDIFF(yy, -1,getdate()), 0)
0

Featured Post

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!

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

872 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