I'm using getdate() to get today's date as a criteria on a query. How Can I
select the date from a week ago. In Acces I use Date()-7 but on sql
getdate()-7 is not working.
WHERE MyDateColumn = CONVERT(varchar(10), GETDATE(), 112) - 7
Hope this helps.
SQL FAQ links (courtesy Neil Pike):
(faqxxx.zip in lib 7)
Quote:> I'm using getdate() to get today's date as a criteria on a query. How Can
> select the date from a week ago. In Acces I use Date()-7 but on sql
> getdate()-7 is not working.
CONVERT(char(8), GETDATE()-7, 112)
As Greg posted, you can use DATEADD too.
Hope this helps.
> SELECT *
> FROM MyTable
> WHERE MyDateColumn = CONVERT(varchar(10), GETDATE(), 112) - 7
> Hope this helps.
> SQL FAQ links (courtesy Neil Pike):
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
> > I'm using getdate() to get today's date as a criteria on a query. How
> > select the date from a week ago. In Acces I use Date()-7 but on sql
> > getdate()-7 is not working.
> > Thanks
I am very new to SQL Server so please bear with me. I am sure it is
version 7.0 though (oh lordie I hear you call cry!).
Anyway...here are my difficulties: I imported a lot of data from
Access to SQL, using the SQL import thing in Enterprise Manager. One
of my tables relies strongly on, what was, a Date/Time field in Access
which I now notice is a smalldatetime field.
The dates in this field are displayed, in Enterprise Manager anyway,
as dd/mm/yyyy although, through tinkering, I noticed that they are
actually stored in the database as yyyy-mm-dd. (I'm presuming they are
displayed as dd/mm/yyyy according to my regional settings?)
Ok, so I was trying to create a web page that would display any item
that had "today" as the date (changing dynamically as each day passes
of course). I used to do this in VBScript / ASP using the line:
SELECT * FROM tablename WHERE datefield = Date()
(or was it Now() - oh crikey!)
Anyway, it used to work fine like that. In moving to SQL I decided
that I would just create a view that already ran this query, and then
just have my page say SELECT * FROM todaysitemstable. This, I felt,
would be the most efficient way of presenting this data (all advice
I chased up the manual, online support etc and discovered that I
needed to use GETDATE() when trying this same thing in SQL. I
couldn't (can't!) get this to work though because, I THINK, the
smalldatetime type saves "to the minute", and yet my old data was
simply dd/mm/yyyy. The upshot being that when I asked for matches for
GetDate() it was saying to me "there are no items that match
yyyy-mm-dd 00:00" (yes, I know, computers can't talk to you!).
Further investigation, and I tried converting mydate and getdate into
varchar types (varchar(10)) like so:
WHERE (CONVERT(varchar(10), mydate, 101) = CONVERT(varchar(10),
Bingo!! It worked and I was well chuffed. However...I also now need to
add two new items of functionality:
I want to query my table to show me dates that are equal to or greater
than GETDATE(). Tried the logical thing which was to change the = in
the above statement to a >= but it doesn't work. And I think I know
why, I just don't know what to do now!?
I am presuming that, because I am converting to a varchar value that
this changes the data to a string - dd/mm/yyyy (eg. 21/03/2003). So
trying to perform a mathematical command on a string is pretty much
useless? Is that why it isn't working? Can someone help in terms of
letting me know what I need to do?
Also, I would like to show items that occur, or have occured in the
current week which, in my mind, sounds complicated.
All help greatly appreciated. Hope to hear from someone...
12. GetDate() Help