Solved

WHERE datetime  >= ?

Posted on 2006-11-14
12
401 Views
Last Modified: 2011-10-03
i am using this query to get my total tickets for this month...


<cfquery name="gettickets" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
SELECT * FROM tickets
WHERE datetime  >= dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )
AND datetime  < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
AND status = 'closed'
</cfquery>


the 'where' bit of the query was show to me as an example on here and i need to fully understand it because i would like to now do a second statment which gets 'ast months' total tickets?
0
Comment
Question by:pigmentarts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 50 total points
ID: 17937872
your where clause have two parts

this
dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )
give you first day of current month
and
dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
gives you last day of current month
0
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 100 total points
ID: 17937880

with this query

SELECT * FROM tickets
WHERE datetime  >= 2006-11-01 00:00:00.000
AND datetime  < 2006-12-01 00:00:00.000
AND status = 'closed'


what it means is that it selects last month data  

ie data from 01/11/2006         to         01/12/2006

this is done based on todays date (getdate() ).
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17937881
> convert(datetime, convert(varchar(10), getdate(), 120), 120)
this part truncates the time portion from the date, ie sets it to 0:00:00

>datepart(day, getdate())
returns the day of today, ie 14 (14/11/2006).
hence:
>dateadd(day, 1-datepart(day, getdate()), getdate())
will add -13 days from today, hence arrive on 1/11/2006 0:00:00

the additional dateadd(month,1, xxxx ) will simply add 1 month, and arrive on 1/12/2006 0:00:00


0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 28

Expert Comment

by:imran_fast
ID: 17937891
sorry this
dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
gives you first day of next month


you can always use work around for above query

where datediff(m, datetimefield, getdate()) =0
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937900
ok so if i wanted to backtrack last months i.e. get the first day of this month and backtrack 28 days or so.


i understand what its doing as a whole but if i could brake down what each bit of the code does then maybe i could workout how to get last months set of data also.
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937904
sorry only just seen the other posts
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17937963
it this dynamic then

(day, 1-datepart(day, getdate()), getdate())

i.e. the 13 days it added based on whats left?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17938502
(day, 1-datepart(day, getdate()), getdate())
i.e. the 13 days it added based on whats left?

today we are the 14th, so 1-14 = -13.  
ie, go back 13 days from today gives the 1st.

if today we are the 21st, the formula will do
1-21 = -20, going back by 20 also gives the 1st...
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17939538
so could i say get last month with the same statment?

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 350 total points
ID: 17940677
yes:


dateadd(monht,-1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
give you first day of last month

and
dateadd(month, 0, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
gives you first day of current month. with the < this will give you all records for the last month
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17941166
seems like its working ok now

<cfquery name="getticketsLast" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
SELECT * FROM tickets
                 
WHERE datetime <=dateadd(month,-1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
AND datetime  > dateadd(month, 0, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
</cfquery>
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 17941190
sorry this...

<cfquery name="getticketsLast" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
SELECT * FROM tickets
WHERE datetime >=dateadd(month,-1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
AND datetime  < dateadd(month, 0, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
</cfquery>
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 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