GetDate function in SQL 2000

Posted on 2008-10-14
Medium Priority
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.

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

Question by:kpwhitte
LVL 143

Accepted Solution

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

Expert Comment

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

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 :)


A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Author Comment

ID: 22714087
 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.

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

Author Comment

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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

607 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