Solved

WHERE datetime  >= ?

Posted on 2006-11-14
12
389 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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