GetDate function in SQL 2000

I need to transfer data to a flat file from SQL 2000 query. The txt file need to contain the previous days data. I dont get any results using the following code.

What am I missing?

I also need to export data which will create a new file with each days date.

Please help with either or both.

SELECT     TOP 100 PERCENT dbo.Transactions.[Date] AS [Trans Date], dbo.Card.Name, dbo.Transactions.TransTotal, dbo.Transactions.TransNum, 
                      dbo.Transactions.AcctNum, dbo.Card.User1, dbo.Transactions.CostCode, dbo.Transactions.DebitAccount, dbo.Card.CardNumber
FROM         dbo.Transactions INNER JOIN
                      dbo.Card ON dbo.Transactions.Card1 = dbo.Card.CardNumber
WHERE     (dbo.Transactions.[Date] = GETDATE())
ORDER BY dbo.Transactions.[Date] DESC

Open in new window

Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>WHERE     (dbo.Transactions.[Date] = GETDATE())

should be, for TODAY date:

WHERE dbo.Transactions.[Date] >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120)
  AND dbo.Transactions.[Date] < dateadd(day, 1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120))

for yesterday

WHERE dbo.Transactions.[Date] < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120)
  AND dbo.Transactions.[Date] >= dateadd(day, -1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120))
GetDate() returns the exact time and date that the function is called.  What you'll need to do is take todays date time, then remove one day, then compare the result formated MM/DD/YYYY to the date in the table formated MM/DD/YYYY.  It might look something like

WHERE     (Convert(varchar, dbo.Transactions.[Date], 101) = Conver( varchar, DateSub(Day, 1, GETDATE()), 101)

Open in new window

Patrick MatthewsCommented:
Hello kpwhitte,

To explain why aneglIII's answer works, GETDATE() returns the current date AND time, to within a couple
if miliseconds.  The likelihood of having transaction records that exactly match the current system date
and time is almost always essentially zero :)


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kpwhitteAuthor Commented:
 Works perfect. Now how do I DTS the data to a flat file with a unique name.

I have the DTS written to export to one file, but it is overwritten everyday. I need to save data for each day.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
for that, you need to use a dts global variable, which get's filled with a initial (active-x) step, and that variable is then used in the file destination configuration.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assume you actually want to accept some comments as answer, rather than accept your own comment as answer?
kpwhitteAuthor Commented:
Indeed. EE has been great. I inadverdently accepted where I should not have. I will attempt to remidy.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.