Date calculations made easier - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date calculations made easier

Author

Topic

PeterBij45
Starting Member

5 Posts

Posted - 2003-01-23 : 11:07:23

We want a query to have an automatic date calculation
like
Between "first day of this month" and "last day of this month"

We came up with this part of a Where-clause, but we don't know if someone knows a more simplefied way to handle this problem.

Between
Convert(smalldateTime,(convert(Integer,getdate()- day(GetDate())))) And
Convert(smalldatetime,(convert(Integer,dateadd(m,1,(getdate() - day(getdate()))))))

The problem here is that the conversion of integer into SmallDateTime still gives the 00:00:00 time value with it.

Kind regards,

Peter Bij

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-01-23 : 11:15:05

Instead of converting dates to numbers and doing calculations, use the date functions such as datediff, dateadd etc

See books online for more details

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-23 : 11:15:46

small date time evil .. unless your ok with the year 2050 bug ... :)

why are you converting it to integer anyway?

BETWEEN CONVERT(SMALLDATETIME, DATEADD(d, 0 - DAY(GETDATE()), GETDATE())) AND CONVERT(SMALLDATETIME, DATEADD(m, 1, DATEADD(d, 0 - DAY(GETDATE()), GETDATE())))

odd way to get a month view :)

PeterBij45
Starting Member

5 Posts

Posted - 2003-01-23 : 11:23:15

quote:

small date time evil .. unless your ok with the year 2050 bug ... :)

why are you converting it to integer anyway?

BETWEEN CONVERT(SMALLDATETIME, DATEADD(d, 0 - DAY(GETDATE()), GETDATE())) AND CONVERT(SMALLDATETIME, DATEADD(m, 1, DATEADD(d, 0 - DAY(GETDATE()), GETDATE())))

odd way to get a month view :)

The conversion is used to turn down the time-value of your dateadd function
If you have this in a select list you will see that the result is something like 2002-12-31 17:20:00, but with our methode the result is 2003-01-01 00:00:00, And unfortunately the Time matters in this query

Kind regards,

Peter Bij

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-23 : 11:23:18

What's the 2050 bug?

EDIT: there are much faster ways to get "just the date part" of a datetime ... converting it is the slow way ...

Here's a good way from ArnoldFribble

select dateadd(dd,datediff(dd,0,getdate()),0)

Jay White
{0}

Edited by - Page47 on 01/23/2003 11:25:55

PeterBij45
Starting Member

5 Posts

Posted - 2003-01-23 : 11:30:51

quote:

What's the 2050 bug?
<Start Answer>
two digit year cutoff Option
Use the two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.

A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
----------

Note Microsoft SQL Server uses 2049 as the cutoff year for interpreting dates; OLE Automation objects use 2030. You can use the two digit year cutoff option to provide consistency in date values between SQL Server and client applications. However, to avoid ambiguity with dates, use four-digit years in your data.

<End Answer>

EDIT: there are much faster ways to get "just the date part" of a datetime ... converting it is the slow way ...

Here's a good way from ArnoldFribble

select dateadd(dd,datediff(dd,0,getdate()),0)

Jay White
{0}

Edited by - Page47 on 01/23/2003 11:25:55

Kind regards,

Peter Bij

PeterBij45
Starting Member

5 Posts

Posted - 2003-01-23 : 11:33:33

quote:

quote:

What's the 2050 bug?
<Start Answer>
two digit year cutoff Option
Use the two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.

A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
----------

Note Microsoft SQL Server uses 2049 as the cutoff year for interpreting dates; OLE Automation objects use 2030. You can use the two digit year cutoff option to provide consistency in date values between SQL Server and client applications. However, to avoid ambiguity with dates, use four-digit years in your data.

<End Answer>

EDIT: there are much faster ways to get "just the date part" of a datetime ... converting it is the slow way ...

Here's a good way from ArnoldFribble

select dateadd(dd,datediff(dd,0,getdate()),0)

Jay White
{0}

Edited by - Page47 on 01/23/2003 11:25:55

Kind regards,

Peter Bij

Jay
This is a fine start to find the first day of this month, but how do you calculate the lastday of this month

Kind regards,

Peter Bij

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-23 : 11:43:31

subtract a day from the first day of next month

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-23 : 11:50:57

Like this:

SELECT DateAdd(mm, DateDiff(mm, 0, getdate()), 0) AS FirstOfMonth,
DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, getdate())+1, 0)) AS LastOfMonth

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-23 : 12:21:59

...or...


select
    dateadd(dd,datediff(dd,0,getdate()),0) - day(dateadd(dd,datediff(dd,0,getdate()),0)) + 1 as 'First Day of Month',
    dateadd(mm,1,dateadd(dd,datediff(dd,0,getdate()),0)) - day(dateadd(mm,1,dateadd(dd,datediff(dd,0,getdate()),0))) as 'Last Day of Month'

Jay White
{0}

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-23 : 17:56:55

Originally Posted By Arnold Fribble

declare @d datetime
set @d = getdate()

/*First Day of Month*/
SELECT dateadd(mm,datediff(mm,0,@d),0)

/*Last Day Of Month*/
SELECT dateadd(mm,datediff(mm,0,@d) + 1,0) - 1

Oops, I rob's looked different at first but now that i take a close look it's actually the same except for the implied dateadd which here shows as -1 and rob has as dateadd(day,,-1)

Edited by - ValterBorges on 01/23/2003 17:57:25

Edited by - ValterBorges on 01/23/2003 23:42:20

PeterBij45
Starting Member

5 Posts

Posted - 2003-01-24 : 11:14:33

quote:

Originally Posted By Arnold Fribble

declare @d datetime
set @d = getdate()

/*First Day of Month*/
SELECT dateadd(mm,datediff(mm,0,@d),0)

/*Last Day Of Month*/
SELECT dateadd(mm,datediff(mm,0,@d) + 1,0) - 1

Oops, I rob's looked different at first but now that i take a close look it's actually the same except for the implied dateadd which here shows as -1 and rob has as dateadd(day,,-1)

Edited by - ValterBorges on 01/23/2003 17:57:25

Edited by - ValterBorges on 01/23/2003 23:42:20

Thank you all for your responses and knowledge.
The problem is solved now.
Kind Regards

P. Bij

Kind regards,

Peter Bij

r937
Posting Yak Master

112 Posts

Posted - 2003-01-24 : 14:24:50

okay, what am i missing? between the first and last day of this month? like, the year and month are the same?

i can understand all the date calcs if the purpose is to see whether a column value is between but not equal to the first and last days of this month, because then you have to exclude the 1st (no prob, just use the 2nd) and the last day (uh oh...) from the range...

... but nobody ever means it that way, excluding the first and last

so if all you want is to see whether a date column is between (and possibly equal to) the first and last day of this month, why do you need the day at all?

try this --

where convert(char(6),datecol,112)
= convert(char(6),getdate(),112)

yeah, i know, CONVERT is bad, eh

i suppose you could also say

where year(datecol)*100+month(datecol)
= year(getdate())*100+month(getdate())

rudy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources