Getdate

Getdate

Post by Juan » Wed, 09 May 2001 10:47:12



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.

Thanks

 
 
 

Getdate

Post by Stride » Wed, 09 May 2001 11:44:28



Quote:> 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.

select dateadd(day, -7,  getdate())
Quote:> Thanks


 
 
 

Getdate

Post by Dan Guzma » Wed, 09 May 2001 11:47:06


The SQL Server GETDATE function returns both date and time.  If the data you
are comparing contain only date (time '00:00:00'), you can use the CONVERT
function to extract only the date portion from GETDATE.  For example:

SELECT *
FROM MyTable
WHERE MyDateColumn = CONVERT(varchar(10), GETDATE(), 112) - 7

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:> 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.

> Thanks

 
 
 

Getdate

Post by Dan Guzma » Wed, 09 May 2001 21:53:37


Sorry, my previous post was not correct.  The CONVERT should have been:

    CONVERT(char(8), GETDATE()-7, 112)

As Greg posted, you can use DATEADD too.

Hope this helps.


> The SQL Server GETDATE function returns both date and time.  If the data
you
> are comparing contain only date (time '00:00:00'), you can use the CONVERT
> function to extract only the date portion from GETDATE.  For example:

> SELECT *
> FROM MyTable
> WHERE MyDateColumn = CONVERT(varchar(10), GETDATE(), 112) - 7

> Hope this helps.

> -----------------------
> SQL FAQ links (courtesy  Neil Pike):

>  http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
>  (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
Can
> I
> > select the date from a week ago.  In Acces I use Date()-7 but on sql
> > getdate()-7 is not working.

> > Thanks

 
 
 

1. GETDATE() problem with smalldatetime (where mydate = getdate()?)

Hi.

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
greatly appreciated!).

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:

SELECT     *
FROM         dbo.tablename
WHERE     (CONVERT(varchar(10), mydate, 101) = CONVERT(varchar(10),
GETDATE(), 101))

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...
Regards
Nathon

2. VFP 5.0: Rich Text Control

3. getDate

4. "@@NestLevel" Question

5. Getdate() question

6. DB name with a '-'

7. GETDATE() can't be used in functions?? :(

8. Dates Intervals

9. A Useful Date Conversion Function -- Problem Initializing Default Function Value to Getdate()

10. how to cut off the time part in getdate()

11. CONVERT(datetime,GETDATE(),101) !!!!!!

12. GetDate() Help

13. getdate() function