Date - 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

General SQL Server Forums

New to SQL Server Programming

Date

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-04-25 : 03:24:18

Is there anyway i can get the result for between date?
Below not working. I do not want to specific the date.

select between DATEADD(Day,-1, datediff(day, 0, getdate()) )
and DATEADD(Day,0, datediff(day, 0, getdate()) )

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-25 : 03:28:33

quote:
Is there anyway i can get the result for between date?
Below not working. I do not want to specific the date.

select between DATEADD(Day,-1, datediff(day, 0, getdate()) )
and DATEADD(Day,0, datediff(day, 0, getdate()) )

Sorry that looks senseless...
Can you tell us what you want to do?
Is there a table involved?

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 03:30:56

make use of F_TABLE_DATE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-04-25 : 03:53:18

today's date is 25 Apr.
I would like to get from 21 Apr to 24 Apr.

select * from table1
where

buydate between DATEADD(Day,-1, datediff(day, 0, getdate()) )
and DATEADD(Day,0, datediff(day, 0, getdate()) )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 04:05:59

[code]
SELECT [DATE]
FROM F_TABLE_DATE
(
dateadd(day, -4, getdate()),
dateadd(day, -1, getdate())
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-25 : 04:14:43

quote:
Originally posted by peace

Is there anyway i can get the result for between date?
Below not working. I do not want to specific the date.

select between DATEADD(Day,-1, datediff(day, 0, getdate()) )
and DATEADD(Day,0, datediff(day, 0, getdate()) )

The formula above will give you the rows that are dated yesterday.
What date range do you want instead?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-25 : 04:18:24

[code]SELECT *
FROM dbo.Table1
WHERE BuyDate >= DATEADD(DAY, DATEDIFF(DAY, '19000105', GETDATE()), '19000101')
AND BuyDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')[/code]

N 56°04'39.26"
E 12°55'05.63"

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-04-25 : 04:38:03

is an automate daily process where i will get the date 3 days before to 1 day before. i cannot set the date as it will change everyday right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-25 : 04:46:05

And that's exactly what the GETDATE() does.

N 56°04'39.26"
E 12°55'05.63"

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-04-25 : 05:56:35

may i know what this meant?

19000105

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 06:04:51

quote:
Originally posted by peace

may i know what this meant?

19000105

Date 1900 January 5

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-25 : 06:06:11

It's the 5th of January 1900.

What you do is to take the number of days between th 5th of January 1900 and today (GETDATE).
This difference in days (ex 40606 days) is added to the 1st of January 1900 (4 days earlier) to produce a new date that is also 4 days earlier than today.

N 56°04'39.26"
E 12°55'05.63"

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-04-25 : 21:55:15

quote:

Originally posted by SwePeso

SELECT	*
FROM	dbo.Table1
WHERE	BuyDate >= DATEADD(DAY, DATEDIFF(DAY, '19000105', GETDATE()), '19000101')
	AND BuyDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')

N 56°04'39.26"
E 12°55'05.63"

Okay, I get what you meant. If would like to add in the time, can I do that?

Example: today is 26 Apr, get the date from 21 Apr at 4pm to 25 Apr 1159am.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-25 : 22:03:51

just + the time string in HH:MM format


DATEADD(DAY, DATEDIFF(DAY, '19000105', GETDATE()), '19000101') + '16:00'

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-26 : 00:34:16

[code]SELECT *
FROM dbo.Table1
WHERE BuyDate >= DATEADD(DAY, DATEDIFF(DAY, '19000106', GETDATE()), '19000101 16:00')
AND BuyDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101 12:00')[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources