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
 yesterday's info

Author  Topic 

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 11:59:18
how do i get yesterdays records only?

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-05-25 : 12:01:23
Does your table have datetime column?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-05-25 : 12:03:26
[code]where datecol > dateadd(day, datediff(day, 0, getdate()), -1)[/code]

KH
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-25 : 12:41:13
Just yesterdays? Or including today's also?
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 13:31:59
khtan,
looks like it doesn't work inside an open query.
select top 1 * from openquery(dccs_danville, 'select * from downloadeddatetime where downloadeddatetime >dateadd(day, datediff(day, 0, getdate()), -1)')
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-25 : 13:34:58
"open query" won't make a difference in this instance.

You have a table called "downloadeddatetime" which has a column which is also called downloadeddatetime, is that correct?

If [downloadeddatetime] could have a time of midnight, then you need to use ">=" instead of just "="

Kristen
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 13:39:47
my bad the table is called [tblpicktickethdr]

select top 1 * from openquery(dccs_danville, 'select * from [tblpicktickethdr]
where downloadeddatetime >dateadd(day, datediff(day, 0, getdate()), -1)')

But still getting the same error.

Even with this: It shows the same error.
select top 1 * from openquery(dccs_danville, 'select * from [tblpicktickethdr]
where downloadeddatetime > getdate()')
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-25 : 13:45:08
"But still getting the same error"

What is the error?

You didn't try my suggestion about ">=" then?

What does

SELECT * from [tblpicktickethdr] where downloadeddatetime >= '20070524'

give you (i.e. if you run it directly on "dccs_danville")? If there are zero results then there is no data in that range.

Kristen
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 14:03:14
kristen,
[easysoft][interbase] dynamic sql error, sql error code = -804, function unknow, getdate.

SELECT * from [tblpicktickethdr] where downloadeddatetime >= '20070524'
conversion error from string "20070202"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-25 : 14:12:00
Yeah, well interbase presumably doesn't have a GetDate() function for todays date. May well not have DATEADD or DATEDIFF either.

It would have saved folk here time if you had mentioned that you were querying a non-SQL Server database, given that this is a SQL Server only forum!

I have NO IDEA how the query

SELECT * from [tblpicktickethdr] where downloadeddatetime >= '20070524'

gives the error:

conversion error from string "20070202"

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-25 : 14:13:28
What's your connection with SQL USA? (as per the image in your Profile)
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 14:39:23
is there any way to do it without using the functions?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-25 : 14:42:11
Well it doesn't seem to work even with a CONSTANT value for yesterday's date, so I can't see how a function would improve that, but it seems reasonable to me that "interbase" would have a mans of getting the "now" Date & time, and manipulating date/time to add/subtract a day etc.

Either way, I know nothing about "interbase" so I can't help further.

You didn't answer my question:

"What's your connection with SQL USA? (as per the image in your Profile)"

Kristen
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 16:12:55
i can do this.

select * from openquery(dccs_danville, 'select downloadeddatetime
from tblpicktickethdr
where downloadeddatetime >= ''25-may-2007''')
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-05-25 : 16:21:08
i'm surprised that you can't use ISO format for dates:
yyyyMMdd

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-25 : 16:23:33
yeah. its interesting. I wonder if im able to get yesterdays info now.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-05-25 : 16:37:07
well... are you?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-26 : 03:18:18
Clearly you could just create the openquery as a text string.

Have you checked the Interbase manual for their "safe format" for dates?

Kristen
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-26 : 17:51:04
create openquery as a text string? you mean using dymanic sql?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-05-26 : 18:04:02
openquery can't accept variables.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts
Posted - 2007-05-26 : 19:07:03
so i cant get yesterdays info?
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site