Solved

WHERE datetime  >= ?

Posted on 2006-11-14
12
397 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Remove () 10 41
SQL Lag Function DateDiff 2 26
SQL Server Agent "Access Denied" Error 3 36
T-SQL: need to reset a declared variable 4 33
I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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