dbf sql vb

how to check files without checking the past dates
for example
select empno,datetime from badge.dbf where date == getdate - 20000 secs ?? i don't know ?
it's on a vb program
kennethzxcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
SQL is used to check dates inside the database columns, not dates of files.

For example, if you have a table called emp and a field on this table called hire_date which its datatype is datetime, then you can do something like:

SELECT hire_date
FROM emp
WHERE hire_date > DateAdd(getDate(),3,'day');


But if what you want is to access to the date of a file from SQL-Server (which is a little bit strange for me) then you should use some code like this:

CREATE PROCEDURE list_file_info

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Msg VARCHAR(100), @Return INT
DECLARE @FileDate VARCHAR(20), @FileSize VARCHAR(10)


SET DATEFORMAT MDY

IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList

CREATE TABLE #dirlist (FName VARCHAR(1000))

CREATE TABLE #Errors (Results VARCHAR(1000))

-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\*.*'

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END

-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL

--SELECT * FROM #DirList

-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName, SUBSTRING(FName,1,20) AS
FileDate, SUBSTRING(FName,30,10) AS FileSize
FROM #dirlist
WHERE FName NOT LIKE '%<DIR>%'

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname,@FileDate,@FileSize
WHILE (@@fetch_status = 0)
BEGIN


Print @FName + ' ' + @FileDate + ' ' + @FileSize


FETCH NEXT FROM curDir INTO @Fname,@FileDate,@FileSize
END

CLOSE curDir
DEALLOCATE curDir

DROP TABLE #DirList
DROP TABLE #Errors

RETURN @Error

On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO

Open in new window


Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kennethzxcAuthor Commented:
it's an attendance so the dbf is passing data to the vb and i query in the vb about the dbf and pass the value to sql server .
so i just want to pass the values that are inputed in the dbf where curddate or the date and time now - 20000 secs :D it's dynamic and its running on a service thanks :D
0
kennethzxcAuthor Commented:
how to subtract date from FoxPro datetime function()??
0
gplanaCommented:
If you want to substract two dates, just use the minus sign between the two dates and you will get the difference in days:

SELECT (endTime - startingTime) as nDays
FROM your table;
0
gplanaCommented:
but if what you want is to add or substract a number of seconds from a dateTime field, look at this Microsoft Article: http://support.microsoft.com/kb/176043/en
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.