Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
--
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
--
Message posted via http://www.sqlmonster.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
--
Message posted via http://www.sqlmonster.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
--
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
--
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> >I have a view that I'm building in which I need to get all records that
> >have
> > an InvoicedDate in in the past 30 days. So basically I set up part of
the
> > where clause as:
> >
> > (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> >
> > Which doesn't work because the function returns a date and a time,
whereas
> > I'm only storing dates in the InvoicedDate field. So, long story short,
> > it
> > doesn't get the dates from exactly 30 days ago. I could roll some kind
of
> > hack where I add one I guess, but that seems really hokey. What's a
> > better
> > way to accomplish this?
> >
> > Thanks!
> > James
> >
> >
>