• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

SQL 2008 DATES

Hello,
I use below to get comparison data for my Invoices,
I need some help to get the data for last year, same dates.

      -- 1st to 15th of current month --  between (select convert(varchar(10), dateadd(dd,datediff(dd,datepart(dd,getdate()),1),getdate()), 101)) AND (select convert(varchar(10), dateadd(dd,datediff(dd,datepart(dd,getdate()),15),getdate()), 101))
      
       -- 16th to MOnth End of last month  --  between (select convert(varchar(10), DATEADD(m, DATEDIFF(m, 0, getdate()) -1, 15), 101)) AND  (SELECT CONVERT(VARCHAR(10),(DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))),101))

Any help is appreciated.
0
W.E.B
Asked:
W.E.B
  • 7
  • 4
  • 2
  • +1
1 Solution
 
aprestoCommented:
Can you use the Year() and day() functions? i.e

WHERE Year(Datefield) = Year(GetDate() AND Month(Datefield) <= 15

WHERE Year(Datefield) = Year(GetDate() AND Month(Datefield) > 15)

As a quick example
0
 
tim_csCommented:
Could just add in one more DATEADD for years.

select convert(varchar(10), DATEADD(yy,-1,dateadd(dd,datediff(dd,datepart(dd,getdate()),1),getdate())), 101)


select convert(varchar(10), DATEADD(yy,-1,dateadd(dd,datediff(dd,datepart(dd,getdate()),15),getdate())), 101)
0
 
W.E.BAuthor Commented:
Hello,
I tried your code, not working

Select ------

WHERE
      TransactionType = 1 AND ISNULL(AR.Cancelled,0) = 0 AND
      C1.AccountNumber Not in (6,511,818) AND
      Year(TransactionDate) = Year(GetDate() AND Month(TransactionDate) <= 15
I also tried
TransactionDate = Select (Year(TransactionDate) = Year(GetDate() AND Month(TransactionDate) <= 15)

thanks
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
aprestoCommented:
I think it should be:
....
Year(TransactionDate) = Year(Getdate())  AND Month(TransactionDate) = 6 AND Day(TransactionDate) <= 15)

Assuming you want to get THIS years data where the day is Less than 15 in the month of June (June plucked from the sky :))
0
 
lwadwellCommented:
have a look at:
select getdate()
     , DATEADD(d, 14, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))     as [15th_current]
     , DATEADD(d, 15, DATEADD(m, DATEDIFF(m, 0, GETDATE())-1, 0))   as [16th_last_month]
     , DATEADD(d, 15, DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0))  as [16th_last_year]

Open in new window

0
 
W.E.BAuthor Commented:
Hello,
I tried
      TransactionDate Between  (select convert(varchar(10), DATEADD(m, DATEDIFF(m, 0, getdate()) -1, 15), 101)) AND  (SELECT CONVERT(VARCHAR(10),(DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))),101))
      AND (Select convert(varchar(10), DATEADD(yy,-1,dateadd(dd,datediff(dd,datepart(dd,getdate()),1),getdate())), 101))

I get error
Msg 4145, Level 15, State 1, Line 15
An expression of non-boolean type specified in a context where a condition is expected, near ')'.


I tried
Year(TransactionDate) = Year(Getdate())  AND Month(TransactionDate) = 6 AND Day(TransactionDate) <= 15

with this, I will have to change the month manually.

thanks
0
 
tim_csCommented:
TransactionDate BETWEEN CONVERT(VARCHAR(10), DATEADD(yy, -1, DATEADD(dd, DATEDIFF(dd, DATEPART(dd, GETDATE()), 1), GETDATE())), 101)
                    AND     CONVERT(VARCHAR(10), DATEADD(yy, -1, DATEADD(dd, DATEDIFF(dd, DATEPART(dd, GETDATE()), 15), GETDATE())), 101)
0
 
W.E.BAuthor Commented:
Tim,
your code worked,
can you please help with the  16th to end of month,

thanks,
0
 
lwadwellCommented:
Did you look at my example date values?
0
 
W.E.BAuthor Commented:
Hi  lwadwell,
your code look easier to understand,

how do you get the first of day of the month of last year
how do you get the 15th of day of last month of last year

thanks
0
 
W.E.BAuthor Commented:
also lwadwell,
last day of last month of this year
and last day of last month of last year
thanks
0
 
lwadwellCommented:
first day of which month last year?

But essentially ...
    DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
gives you the first of the current month ... just add or subtract months/days from there.
For example
- to get the 15th of the current month (1 + 14 = 15):
    DATEADD(d, 14, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
- first of last month:
    DATEADD(m, DATEDIFF(m, 0, GETDATE())-1, 0)
- first of this month last year
    DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0)
0
 
W.E.BAuthor Commented:
Hi  lwadwell,
ok, what about that last day of the month.

Previuos Month last date
Previuos Month last date from last year

Example,
we are in September ,
so I need August 31st, 2012
and August 31st, 2011

thanks
0
 
lwadwellCommented:
As DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) returns the 1st ... less 1 day is the last day of the previous month e.g.
    DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
and
    DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0))
0
 
W.E.BAuthor Commented:
Excellent,
thank you.
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.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now