Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

WHERE datetime  >= ?

Posted on 2006-11-14
12
Medium Priority
?
405 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 200 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 400 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 1400 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 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