[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

how do you subtract an alotted time from a Date

I want to retrieve all the records in a database from say the previous 6 months  so how would i subtract 6 months from the current date to then determine compare to each date

This isn't real code but just to explain what i mean

function getDate()
   currentsystemDate - 6 months = newDate
   return newDate
end function

function findDatabaseEntries(Row as DataRow)
row.Item(date) > newDate
end function

0
ethnarch
Asked:
ethnarch
1 Solution
 
RonaldBiemansCommented:

private function getDate() as date
   return now.AddMonths(-6)
end function
0
 
MajinLokiCommented:
Public Function getDate() As Date
        Return New Date(Date.Now.Year, Date.Now.Month - 6, Date.Now.Day)
End Function

Of course, you will have to do checks to see if the current day is more than the number of days in the new date and adjust accordingly.  Here's a simple snippet that just takes the last day of the month if this is the case.

Public Function getDate() As Date
        Dim newDate As New Date(Date.Now.Year, Date.Now.Month - 6, Date.Now.Day)
        If Date.DaysInMonth(Date.Now.Year, Date.Now.Month) > Date.DaysInMonth(newDate.Year, newDate.Month) Then
            Return New Date(Date.Now.Year, Date.Now.Month - 6, Date.DaysInMonth(Date.Now.Year, Date.Now.Month - 6))
        Else
            Return New Date(Date.Now.Year, Date.Now.Month - 6, Date.Now.Day)
        End If
End Function
0
 
gangwischCommented:
use the datediff function
is my recommendation
0
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!

 
gopinathdeepakCommented:
if u want to run the query on the backend, this is how u would write an sql statement for the database


SELECT * FROM TABLENAME
WHERE   DATEDIFF(mm, TABLENAME.DATEFIELD, GETDATE()) < 6

or...if u would rather do it on the front end...theres a DateDiff function in the DateTime class in .NET too...it does the same thing...

0
 
ethnarchAuthor Commented:
thanks for all the suggestions, ehh now i just need to find time to try them. Just to let you know I am still here though and reading this.
0
 
ethnarchAuthor Commented:
I have 2 more questions
one of them relates to gopinathdeepak's solution

1.  gopinathdeepak's solution works fine and seems to be pretty simple except, if i am always comparing it to the current date(today's date) however what happens if i wanted to take a date in the past and find every date that is 6 months previous.  Using the DateDiff function would give me all the dates before and after the chosen comparison date.  

this is what i used for gopinathdeepak's
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT custitem.InvoiceNo, custitem.LocationNo, custitem.LineItem, custitem.PLU, " & _
        "custitem.ItemNo, custitem.FullDesc, custitem.Qty, custitem.Matrix_ID, Custinv.Lo" & _
        "cationNo AS Expr1, Custinv.InvoiceDate, Custinv.RegisterNo, Custinv.WorkingDate," & _
        " Custinv.InvoiceTime, Custinv.InvoiceNo AS Expr2 FROM custitem INNER JOIN Custin" & _
        "v ON custitem.InvoiceNo = Custinv.InvoiceNo Where DATEDIFF(mm, Custinv.InvoiceDate, GETDATE()) < 2 And DATEDIFF(dd, Custinv.InvoiceDate, GETDATE()) < 2", DataCon)

2.  I also tried it this way and it doesn't seem to work correctly possibly i am entering something in correctly the sql exception error tells me there is a syntax error after 11 on line 1

Dim mnthsPrior As Date = Today.AddMonths(-4)

Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT custitem.InvoiceNo, custitem.LocationNo, custitem.LineItem, custitem.PLU, " & _
        "custitem.ItemNo, custitem.FullDesc, custitem.Qty, custitem.Matrix_ID, Custinv.Lo" & _
        "cationNo AS Expr1, Custinv.InvoiceDate, Custinv.RegisterNo, Custinv.WorkingDate," & _
        " Custinv.InvoiceTime, Custinv.InvoiceNo AS Expr2 FROM custitem INNER JOIN Custin" & _
        "v ON custitem.InvoiceNo = Custinv.InvoiceNo Where Custinv.InvoiceDate =" & mnthsPrior, DataCon)
0
 
gopinathdeepakCommented:
DateDiff(mm, FIELDNAME, GETDATE()) is used to compare with todays date..

however if u want to select the date from which u wanna get 6 months prior data, u could send that date as a parameter..

eg:

create procedure myproc
@compdate date
as
SELECT * FROM TABLENAME
WHERE   DATEDIFF(mm, TABLENAME.DATEFIELD, @compdate) < 6

GO


this should work...just pass the parameter from VB.NET using SqlCommand.Parameters.Add("@compdate", datecontrol.value)

let me know what u think..


0
 
ethnarchAuthor Commented:
where do i create the procedure i have never used a stored procedure before in sql, so where would i define it?
0
 
gopinathdeepakCommented:
ok...

if u're using SQL Server full version with tools..u should have SQL Enterprise Manager...it is a tool with which u can browse through the database and its objects (tables, views, stored procedures etc..)...there u can go in Stored Procedures and double click on the name of the stored procedure u are looking for and make the changes there...

if u dont have the full version, go to command prompt..and execute the osql commnd

for trusted connectin..
-osql -E

for untrusted connection
-osql -U username -P password

this will bring u to a prompt that says 1>

here u have to manually put in sql commands...for altering the procedure use the alter procedure command...

ALTER PROCEDURE procdeurename
@parameter1 parametertype,
@parameter2 parametertype

AS

SELECT *
FROM TABLENAME
WHERE FIELD1 = @parameter1 AND FIELD2 = parameter2

GO

just like that...
when u say GO and hit enter it will execute the series of lines before GO...

or else u could simply..
DROP PROCEDURE procedurename
GO

then...

CREATE PROCEDURE procdeurename
@parameter1 parametertype,
@parameter2 parametertype

AS

SELECT *
FROM TABLENAME
WHERE FIELD1 = @parameter1 AND FIELD2 = parameter2

GO

...same thing...

try it and let me know...

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now