?
Solved

GetDate function in SQL 2000

Posted on 2008-10-14
8
Medium Priority
?
1,060 Views
Last Modified: 2013-11-30
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.

thanks,
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

0
Comment
Question by:kpwhitte
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22713932
>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))
0
 

Expert Comment

by:drspeedo
ID: 22713996
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

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22714008
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 :)

Regards,

Patrick
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:kpwhitte
ID: 22714087
Angel,
 Works perfect. Now how do I DTS the data to a flat file with a unique name.
i.e.
Fuel_Data_10_13_2008
Fuel_Data_10_14_2008
etc.

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

thanks,
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22714125
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.

see http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_23699685.html
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23045147
I assume you actually want to accept some comments as answer, rather than accept your own comment as answer?
0
 

Author Comment

by:kpwhitte
ID: 23068493
Indeed. EE has been great. I inadverdently accepted where I should not have. I will attempt to remidy.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question