Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.yes it can be done through a query, but the standard sql for it will almost certainly not work in whatever database system you're using

date functions are notoriously non-standard, and each database system pretty much has its own proprietary functions

if you wouldn't mind mentioning which database system you're using, we could probably help you|||Nevermind I figured it out. I am using a SQL server 2000 database to query my data. I used the Datediff() function in conjunction with getdate(). See examples below for anyone else needing help with this topic.

DATEDIFF([day], dbo.table.field, GETDATE()) AS DAYS,
DATEDIFF([HOUR], dbo.table.field, GETDATE()) AS HOURS|||moved to SQL Server forum|||The solution you arrived at will work, but will not benefit from any indexing on your dbo.table.field column. If, instead, you used the DateAdd() function to find the date five days prior to the current date, then the optimizer will be able to use an index on dbo.table.field for comparisons:Where dbo.table.field >= DateAdd(day, -5, GetDate())

Need script help w/ dates

Hi,

I have a script where I retrieve the date portion of a filename into an integer variable.

The variable value looks like this: "20070815"

My question is, how can I then take this value and compare it against the current date on the server? Is there any sample code that can show me how to use date functions in this way?

Thanks

You should be able to direclty compare in the query.Something like this for example.

IF GETDATE() > '20070815'

BEGIN

PRINT 'More'

END

<edit>

In query it would be like

select col1,col2 from tbla where dt_col > '20070815'

</edit>

Thanks

|||In your script, you can do:

Dim todaysDate As String = DateTime.Now.ToString("yyyyMMdd")

Then you can just compare to your input date.|||I noticed you said "integer" variable above. If so, then this should work:

Dim todaysDate as Integer = CInt(DateTime.Now.ToString("yyyyMMdd"))|||

Thanks Phil. This should do the trick.

Need MIN date

Hi
I would like to write query to get minimun data from the list of dates. Ie. i have a coumn in the table that conatins all dates from which i need to get minimum date. Please help me.You would like to

select the minimum date from a table

Not certain how to accomplish this

Please post the text book and the page that this question is from so I can better help you|||mark a cross for these dates in a calendar and the first crossed date is the minimum date.|||use min() function|||to use min() function correctly the datatype should be date, if the date is stored in a char field then use convert to make it of date datatype

Need help writing stored procedure involving dates

I am trying to write a stored procedure which would execute following logic:

- The stored procedure takes 2 optional parameters @.StartDate and @.EndDate

@.StartDate Datetime = null
@.EndDate Datetime = null

- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @.StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @.EndDate as the ED and MIN() of the Date field as SD

I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null


AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND LName LIKE ISNULL(@.LName,'%' + NULL)
AND ID LIKE ISNULL(@.ID,'%' + NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

Any help would be appreciated
Thanks in advance :)I'm a little confused. What is your question ?

did you mean except for incoporating NULLS ?

Cheers,
-Kilka|||I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
To make it simple lets say I have a simple table with 3 columns: FName, LName and Date

FName LName Date
Aab 02/05/2005
Abc Bb 02/06/2005
Aaaa Bbb
02/07/2005
Baaaa Bbb
Baaca 02/07/2005
Caa Bbbb 02/07/2005

As can be seen that FName, LName and Date columns accept NULL values.
Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @.FName, @.LName, @.StartDate and @.EndDate and do a search on this table.
I am having trouble handling these NULL fields.

When I execute the stored procedure, I should get results as follows:
- If user enters @.FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
- Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields

I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
Thanks again for your time :)|||Do you really have '%' + NULL in the code? I would think you just want '%'.

Are you getting any data back from this query when you have data that looks like your example?|||You guys are good sorry forgot to update the stored procedure.
No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null

AS

IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND (LName LIKE ISNULL(@.LName,'%') OR LName IS NULL)
AND (ID LIKE ISNULL(@.ID,'%') OR ID IS NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
Any ideas how can I modify the stored procedure to avoid returning null fields.

Thank you again guys, I really appreciate your help|||Do you get the correct results if you take out OR LName IS NULL and
OR ID IS NULL and OR DateInputted IS NULL?

If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.|||First, decide on precedence. What if the user passes all three parameters?

Assuming the precedence is LastName, FirstName, Date:Declare @.t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)

Insert @.t (FirstName, LastName, EndDate)
Select 'Kim', 'Cat', GetDate()
Union
Select 'Pat', 'Dog', Null
Union
Select 'Ted', Null, GetDate()
Union
Select 'Jim', Null, Null
Union
Select Null, 'Fox', GetDate()
Union
Select Null, 'Fox', Null
Union
Select Null, Null, GetDate()
Union
Select Null, Null, Null

Select * From @.t

Declare @.FirstName varchar(3)
, @.LastName varchar(3)
, @.EndDate datetime

Select @.LastName = 'F'

Select *
From @.t
Where (@.LastName Is Not Null And LastName Like @.LastName + '%')
Or
(@.LastName Is Null And @.FirstName Is Not Null And FirstName Like @.FirstName + '%')
Or
(@.LastName Is Null And @.FirstName Is Null And EndDate > = Coalesce(@.EndDate, '01/01/1950') )|||FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.