Solved

WHERE datetime  >= ?

Posted on 2006-11-14
12
386 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
  • 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 142

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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 142

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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now