DATEADD function - 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 2005 Forums

Transact-SQL (2005)

DATEADD function

Author

Topic

adriane
Starting Member

7 Posts

Posted - 2008-05-23 : 15:41:01

I'm using a query that is not retrieving all of the data it should because of the datetime stamp. If I use the following in the where clause:

{table.date} between '4/1/2008' and dateadd(day, 1 '4/30/2008')

that works fine for April but not for other months, where I might get more data from the following month than I should.

Is there a way to add hours, minutes, and seconds (23:59:59), all at the same time, without moving ahead an entire day?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 15:42:58

SELECT *
FROM Table1
WHERE Col1 >= '20080401'
AND Col1 < '20080501'

E 12°55'05.25"
N 56°04'39.16"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-05-23 : 15:45:06

DateAdd(hours, 23, DateAdd(minutes, 59, DateAdd(secinds, 59, GetDate())))

Et viola!

=======================================
Do something for somebody every day for which you do not get paid. -Albert Schweitzer, philosopher, physician, musician, Nobel laureate (1875-1965)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 15:48:39

quote:
Originally posted by Bustaz Kool

DateAdd(hours, 23, DateAdd(minutes, 59, DateAdd(secinds, 59, GetDate())))

Et viola!

And still you don't get the records for the last second of that day.

23:59:59.003
...
23:59:59.997

are not fetched.

And your does not "start" with midnight. It just adds 23 hours, 59 minutes and 59 seconds to the current time.

E 12°55'05.25"
N 56°04'39.16"

adriane
Starting Member

7 Posts

Posted - 2008-05-23 : 16:12:43

Oops, forgot to mention the dates are actually parameters in a stored procedure, sorry about that

quote:
Originally posted by Peso

SELECT *
FROM Table1
WHERE Col1 >= '20080401'
AND Col1 < '20080501'

E 12°55'05.25"
N 56°04'39.16"

adriane
Starting Member

7 Posts

Posted - 2008-05-23 : 16:16:53

This will work great

- 23:59:59 is all I need. I don't see dates in the table with anything more precise, unless I'm missing something.

quote:
Originally posted by Bustaz Kool

DateAdd(hours, 23, DateAdd(minutes, 59, DateAdd(secinds, 59, GetDate())))

Et viola!

=======================================
Do something for somebody every day for which you do not get paid. -Albert Schweitzer, philosopher, physician, musician, Nobel laureate (1875-1965)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 16:17:52

SELECT @UserSuppliedDateTimeParemeter_FromDate = DATEDIFF(DAY, '19000101', @UserSuppliedDateTimeParemeter_FromDate),
@UserSuppliedDateTimeParemeter_ToDate = DATEDIFF(DAY, '18991231', @UserSuppliedDateTimeParemeter_ToDate)

SELECT *
FROM Table1
WHERE Col1 >= @UserSuppliedDateTimeParemeter_FromDate
AND Col1 < @UserSuppliedDateTimeParemeter_ToDate

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 16:21:49

Or if only one parameter is supplied

DECLARE @FromDate DATETIME, @ToDate DATETIME

SELECT @FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @UserSuppliedDateTimeParameter), '19000101'),
@ToDate = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @UserSuppliedDateTimeParameter), '19000101')

SELECT *
FROM Table1
WHERE Col1 >= @FromDate
AND Col1 < @ToDate

E 12°55'05.25"
N 56°04'39.16"

adriane
Starting Member

7 Posts

Posted - 2008-05-23 : 16:33:25

KK, gotcha. Thank you for the fast response!!

quote:
Originally posted by Peso

Or if only one parameter is supplied

DECLARE @FromDate DATETIME, @ToDate DATETIME

SELECT @FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @UserSuppliedDateTimeParameter), '19000101'),
@ToDate = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @UserSuppliedDateTimeParameter), '19000101')

SELECT *
FROM Table1
WHERE Col1 >= @FromDate
AND Col1 < @ToDate

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources