DATE functions

DATE functions

Post by Jim Rupp » Fri, 09 Sep 1994 02:01:00



I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
request: how do you write a date manipulation sequence to find a specific date
7 days prior to the current date ? There does not seem to be any SQL functions
to do this.
 
 
 

DATE functions

Post by Mike Thomas - Sun » Fri, 09 Sep 1994 20:04:27



Quote:> I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
> request: how do you write a date manipulation sequence to find a specific date
> 7 days prior to the current date ? There does not seem to be any SQL functions
> to do this.

Try this:

select dateadd(dd,-7,getdate())

Check out the TSQL Users Guide on date functions.

Cheers,

Mike

 
 
 

DATE functions

Post by cgr.. » Fri, 09 Sep 1994 21:41:12


: I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
: request: how do you write a date manipulation sequence to find a specific date
: 7 days prior to the current date ? There does not seem to be any SQL functions
: to do this.

Use
   select dateadd(day, -7, getdate())

Note that the time is maintained here.  If you want to strip off the time
(make it 0:0:0000), then use
   select dateadd(day, -7, convert(datetime,
                           convert(char(11), getdate(), 101)))

--
Chris Greer                       |    -----
PowerBuilder/Sybase/C Mercenary   |  |  ---|
14127 Long Shadow Dr              |  | |   |__   _ *  _
Houston, TX 77015                 |  | |   |  ||/  | |_
                                  |  |  ---|  ||   | __|
#include <std_disclaimer.h>       |   -------------------

   "Few men have the imagination for reality" - Goethe

 
 
 

DATE functions

Post by Craig K » Sat, 10 Sep 1994 09:27:54


: I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
: request: how do you write a date manipulation sequence to find a specific date
: 7 days prior to the current date ? There does not seem to be any SQL functions
: to do this.

How about

        select columns from table
        where some_date <= dateadd(dd, -7, getdate())

Good luck!

--
+----------------------------------------------------------------------------+
| Craig J. Kim, Sr. Consulting Engineer, Western Data Corp.    (206)493-6742 |

+----------------------------------------------------------------------------+

 
 
 

DATE functions

Post by Q Vincent Y » Sun, 11 Sep 1994 01:41:55



| I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
| request: how do you write a date manipulation sequence to find a specific date
| 7 days prior to the current date ? There does not seem to be any SQL functions
| to do this.

Read about dateadd(), datediff().

--

Q Vincent Yin                   |       Repeat

                                |       Until 0 = 1;

 
 
 

DATE functions

Post by Len Gerst » Sat, 10 Sep 1994 22:26:58



|> I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
|> request: how do you write a date manipulation sequence to find a specific date
|> 7 days prior to the current date ? There does not seem to be any SQL functions
|> to do this.

select dateadd (day, -7, getdate())

 
 
 

DATE functions

Post by Kalen Delan » Mon, 12 Sep 1994 09:49:14



Quote:>I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
>request: how do you write a date manipulation sequence to find a specific date
>7 days prior to the current date ? There does not seem to be any SQL functions
>to do this.

Look at the dateadd( ) function. There are three arguments: the first is
the 'part' of the date you are interested in - mm=month, yy=year, dd =day, etc.
The second argument is how many parts you are interested in, which can be
a negative number. The third is the base date. There is another function
getdate() which returns today's date, so to get the date 7 days ago:

        select  dateadd(dd, -7, getdate() )

I just love the datetime functions. I always give my students really
tricky problems using them. :-)

Kalen Delaney
Sybase Training and Development

 
 
 

DATE functions

Post by Jameison Mart » Mon, 12 Sep 1994 11:49:11


select dateadd(day, -7, getdate())


|> I am creating stored procedures on a Microsoft SQL server using Sybase. I have a simple (hopefully)
|> request: how do you write a date manipulation sequence to find a specific date
|> 7 days prior to the current date ? There does not seem to be any SQL functions
|> to do this.

--
I'd rather be tea-baggin'

 
 
 

DATE functions

Post by Robert Garv » Wed, 14 Sep 1994 04:29:38




Quote:> I am creating stored procedures on a Microsoft SQL server using Sybase.
> I have a simple (hopefully) request: how do you write a date
> manipulation sequence to find a specific date 7 days prior to the
> current date ? There does not seem to be any SQL functions to do this.

You may want rows that have a datetime column value that is any time
within a given day.  The examples thus far would yield only those rows
that have the date portion seven days earlier and the time portion
exactly the same as the current date and time.  So, I would suggest:

select <select_list>
from <table>
where <datetime_column>
    between convert(datetime,
                    convert(varchar, dateadd(day, -7, getdate()),112)
                                                         + ' 00:00:00')
        and convert(datetime,
                    convert(varchar, dateadd(day, -7, getdate()),112)
                                                         + ' 23:59:59')

The convert function format specifier 112 will give the date in the
numeric format yyyymmdd which is unambiguous regardless of the setting
for dateformat.  This example is really simpler than it looks.

- Robert

                                Emeryville, CA  USA 94608-1010

Opinions may be attributed to poster, not necessarily related to Sybase.

 
 
 

DATE functions

Post by Ashutosh Joglek » Thu, 15 Sep 1994 22:13:46


: select <select_list>
: from <table>
: where <datetime_column>
:     between convert(datetime,
:                   convert(varchar, dateadd(day, -7, getdate()),112)
:                                                        + ' 00:00:00')
:       and convert(datetime,
:                   convert(varchar, dateadd(day, -7, getdate()),112)
:                                                        + ' 23:59:59')

: The convert function format specifier 112 will give the date in the
: numeric format yyyymmdd which is unambiguous regardless of the setting
: for dateformat.  This example is really simpler than it looks.

Simple, yes but rather messy.

Please check out the other thread on date functions... Perhaps Sybase
could add a "date-only" datatype ? What do you think ? Make a lot
of lives easier, I feel. The number of programmers who fail to
grasp the need for the above is simply amazing.

Cheers,
Ashu
--

 
 
 

DATE functions

Post by Q Vincent Y » Fri, 16 Sep 1994 11:34:36



Quote:>Perhaps Sybase
>could add a "date-only" datatype ? What do you think ? Make a lot
>of lives easier, I feel. The number of programmers who fail to
>grasp the need for the above is simply amazing.

Agree.  And I also need a time-only datatype.  In PC applications, date and
time are often two different datatypes.  Besides, I need to store the start
and end time of appointments.  Since an appointment can only start and end
in the same day, it's redundent to store the date portion twice.

In addition, it would be nice to have a longdatetime type to go beyond
the Jan 1 1753 limit.

--

Q Vincent Yin                   |       Repeat

                                |       Until 0 = 1;

 
 
 

DATE functions

Post by Mike Thomas - Sun » Fri, 16 Sep 1994 21:56:22




> >Perhaps Sybase
> >could add a "date-only" datatype ? What do you think ? Make a lot
> >of lives easier, I feel. The number of programmers who fail to
> >grasp the need for the above is simply amazing.

> Agree.  And I also need a time-only datatype.  In PC applications, date and
> time are often two different datatypes.  Besides, I need to store the start
> and end time of appointments.  Since an appointment can only start and end
> in the same day, it's redundent to store the date portion twice.

> In addition, it would be nice to have a longdatetime type to go beyond
> the Jan 1 1753 limit.

I also agree with this point, is there anyone from Sybase who could tell us if there are any plans to introduce seperate date and time datatypes??

Mike.


 
 
 

DATE functions

Post by Kalen Delan » Sat, 17 Sep 1994 00:15:58




>In addition, it would be nice to have a longdatetime type to go beyond
>the Jan 1 1753 limit.

It's really not the storage size that determines the early limit, since
we can go forward to Dec 31, 9999! I always taught my classes that it was
because of a calendar shift that occurred in 1752, when the calendar lost
a few days. If we went back before 1753, it would make the date arithmetic
too ambiguous. If someone wanted to know the number of days ago that
Columbus first sighted land,

        select datediff (dd, 'Oct 12 1492', getdate() )

would you want to include the days that were removed or not? So , to
keep things simple, we can't go back further than the calendar shift.

Another poster mentioned that the shift had something to do with the Pope,
and I didn't think this was true, but to set the record straight I
got the following from Microsoft Encarta:

**The Gregorian Calendar
**
**The Julian year was 11 min and 14 sec longer than the solar year.
**This discrepancy accumulated until by 1582 the vernal equinox
**occurred 10 days early and church holidays did not occur in the
**appropriate seasons. To make the vernal equinox occur on March 21,
**as it had in ad 325, the year of the First Council of Nicaea,
**Pope Gregory XIII issued a decree dropping 10 days from the calendar.
**To prevent further displacement he instituted a calendar,
**known as the Gregorian calendar, that provided that century years
**divisible evenly by 400 should be leap years and that all other
**century years should be common years. Thus, 1600 was a leap year,
**but 1700 and 1800 were common years.
**
**  
**
**The Gregorian calendar, or New Style calendar, was slowly adopted
**throughout Europe. It is used today throughout most of the Western
**world and in parts of Asia. When the Gregorian calendar was adopted
**in Great Britain in 1752, another correction of an 11-day discrepancy
**was made; the day after September 2, 1752, became September 14.
**The British also adopted January 1 as the day when a new year begins.
**
**

So now you know!

Kalen Delaney
contractor
Sybase Training and Development

 
 
 

DATE functions

Post by Daniel A. Graif » Sat, 17 Sep 1994 23:03:37



>[...]
>Another poster mentioned that the shift had something to do with the Pope,
>and I didn't think this was true, but to set the record straight I
>got the following from Microsoft Encarta:
>[...]
>So now you know!

Thanks for getting reliable info on this.  I was the "other poster", and
I think I got it pretty close considering I had no reference material.  I
did mistakenly place the adjustment in February instead of September.  I
did note that this adjustment applied to the English speaking world only
(see below).

To get this back to sybase relevance, several poster have asked for
extended date-type fields that would handle dates earlier than 1753.  As
is clear from Mr. Delaney's quote, such a datatype would have to be
localized to reflect differing dates of adoption of the Gregorian
calendar.  The date-arithmetic functions would have to employ a separate
algorithm to reflect the Julian calendar in the earlier periods. It
sounds to me like a lot of complexity for something not very often used.
People who really need date arithmetic before the 19th century can
store the dates as yyyymmdd and write their own calendar functions that
handle the shift appropriately for their application.

Dan
--
Daniel A. Graifer                       Coastal Capital Funding Corp. &
Sr. Vice President, Financial Systems   Franklin Mortgage Capital Corp.
(703)205-5460                           3190 Fairview Park Dr. Suite 200
uucp: uunet!fmccva!dag                  Falls Church, VA 22042

 
 
 

DATE functions

Post by Willard Daws » Mon, 19 Sep 1994 06:34:15



>So , to
>keep things simple, we can't go back further than the calendar shift.

To keep things realistic (that is, modelled after real life, not some
limitation to make life easier on your programmers) , you should ponder
over some of the neat calendar algorithms that do already exist.  We're
not dealing with rocket science here.  For example, there've been more
than a few articles and letters dealing with calendar programs in the
C/C++ Users Journal...  Being the cynic I am, I suppose it'll take a 2/3
vote from ISUG and an act of Congress to get this cleared up.
 
 
 

1. Using a table of dates verses SQL Date Functions

I am considering creating a table that contains an entry for each date that
falls within a specified range.  Each record not only contains the date but
various pieces of information that can be determined by the date such as you
would get when using the SQL Server Date functions such as DatePart().  I am
wondering if anyone knows if creating a table such as this, and joining it
to the SELECT statement would result in faster processing as opposed to
using the Date Functions within the SELECT statement.

Thanks.
Ed

2. HELP : timeout or network fail ???

3. VB Date/Time datatype and VB date functions

4. US-FL-DBA/ORACLE

5. Quesiton about Date Functions in SQL

6. PRACTICAL TRAINING WANTED

7. Error using Format() and Date() functions i reports

8. delphi e-book, eargent

9. date function with sqlserver

10. SQL Satement needed relating to date function

11. sql date function

12. Date Function