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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Using DatePart (I think) in a query

If I have a query based on (see Code below)

This will eventually end up in a stored procedure, I do not need help with the stored procedure, but do need help with getting this part of the query right.

The Month/Day part of the date remains constant, but the Year will change, how do i use date part?

In advance, many thanks for your help.

Regards
ITgirl
SELECT     *
FROM         tms2008.dbo.tblTransactions tx INNER JOIN
                      tblTasks tsk ON tx.TaskCode = tsk.TaskCode
WHERE     (tx.ActivityDate BETWEEN CONVERT(DATETIME, 
                      '2008-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-08-31 00:00:00', 102))

Open in new window

0
ITgirl
Asked:
ITgirl
  • 4
  • 2
1 Solution
 
momi_sabagCommented:
SELECT     *
FROM         tms2008.dbo.tblTransactions tx INNER JOIN
                      tblTasks tsk ON tx.TaskCode = tsk.TaskCode
WHERE     datepart(month,tx.ActivityDate) BETWEEN
                 datepart(month,CONVERT(DATETIME, '2008-04-01 00:00:00', 102) )
         AND datepart(month,CONVERT(DATETIME, '2008-08-31 00:00:00', 102))
and
datepart(day,tx.ActivityDate) BETWEEN
                 datepart(day,CONVERT(DATETIME, '2008-04-01 00:00:00', 102) )
         AND datepart(day,CONVERT(DATETIME, '2008-08-31 00:00:00', 102))

0
 
ITgirlAuthor Commented:
This seems very longwinded - is there not a simpler way, syntactically?
0
 
ITgirlAuthor Commented:
And as I stated the Year i.e. 2008, will not alway be known! This will become a parameter in a Stored Procedure.
0
Industry Leaders: 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!

 
momi_sabagCommented:
you can try

SELECT     *
FROM         tms2008.dbo.tblTransactions tx INNER JOIN
                      tblTasks tsk ON tx.TaskCode = tsk.TaskCode
WHERE     dayofyear(tx.ActivityDate) BETWEEN
                 dayofyear(CONVERT(DATETIME, '2008-04-01 00:00:00', 102) )
         AND dayofyear(CONVERT(DATETIME, '2008-08-31 00:00:00', 102))
0
 
pivarCommented:
Hi,

You don't need datepart, just do this:

declare @year char(4)
set @year='2008'

SELECT     *
FROM         tms2008.dbo.tblTransactions tx INNER JOIN
                      tblTasks tsk ON tx.TaskCode = tsk.TaskCode
WHERE     tx.ActivityDate BETWEEN @year + '-04-01 00:00:00' AND @year + '-08-31 00:00:00'

You may need convert depending on your regional settings.


/peter

0
 
ITgirlAuthor Commented:
Momi_sabag

I have been doing a bit of tinkering and have come up with the following solution, which works much better for me, and will enable me to use the solution within my stored procedure.

Unfortunately the solutions that you have provided, do not really answer my original question, where as my solution below does.

ITgirl
SELECT     *
FROM         tms2008.dbo.tblTransactions tx INNER JOIN
                      tblTasks tsk ON tx.TaskCode = tsk.TaskCode
WHERE     (DATEPART(mm, tx.ActivityDate) IN (4, 5, 6, 7, 8)) AND 
                      (DATEPART(yyyy, tx.ActivityDate) = 2008)

Open in new window

0
 
ITgirlAuthor Commented:
Pivar

Thank you yes that is more along the lines of what I was looking for, and I will use this as my solution.

Many thanks
ITgirl
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now