Fields Within a Certain Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Fields Within a Certain Date

Author

Topic

ncamoens
Starting Member

9 Posts

Posted - 2006-03-11 : 11:05:14

Hello.

I need an SQL Statement for SQL Server in VB.Net that will return values from a table for parts that are due for maintenance within 1 month.

Select * from Part Where DateNOverhaul <= '" & DateAdd(mm,-1,Date) & "' is not working.

I have been trying various solutions on the web and am getting confused because none seem to work. Please help me.

Thank you.

Kristen
Test

22859 Posts

Posted - 2006-03-11 : 11:39:29

Don't you need:

Select * from Part Where DateNOverhaul >= '" & DateAdd(mm,-1,Date) & "'

And what is "Date" defined as? If you need "today's date" use:

GetDate()

instead

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-11 : 11:47:48

What do u mean by not working ?
No results ?
Not expected Results ?
Error?

Did u try the same with a hard coded SQL query in VB.Net side?
DateAdd might not work in VB.Net side (I'm not sure whether that function is available in VB.Net) - If not available, u may need to use some other way to pass the query, or to have a Stored Proc to do the job.

or try using the following (Note that no ampersand, no single quote, ...

Select * from Part Where DateNOverhaul <= GetDate()

Select * from Part Where DateNOverhaul <= DateAdd(mm,-1,GetDate())

ncamoens
Starting Member

9 Posts

Posted - 2006-03-11 : 12:25:19

Hello, thank you.

My code is now :

"Select * from Part where datenover >= DateAdd(month,-1,GetDate()) "

However, this returns all the parts, not the parts due within 1 month.

Changing >= to <= does not return any values.

ncamoens
Starting Member

9 Posts

Posted - 2006-03-11 : 12:33:05

Ooh, hello again.

I fiddled around with it a bit and now it works fine.

It's now
"Select * from Part where datenover between DateAdd(month,-1,GetDate()) and dateadd(month,+1,getdate())"

Thanks again very much for the help. Couldn't have done it otherwise.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-11 : 12:37:07

/* ---- To be deleted, as I posted it after ur comments - Sorry
Can u please give some data and the expected results?

Mainly the data in "datenover" field (which covers all ur possibilities as before one month , within the month, and after a month ...)
*/

Did u realize the error in ur initial problem?

That is : U tried to use TSQL function DateAdd in VB.Net
U were concatenating a string with some function's retuns, expecting that function exist in VB.Net

ncamoens
Starting Member

9 Posts

Posted - 2006-03-11 : 12:46:27

Hello.

Here are some values I've got in the DateNOver field:

2/1/2006
4/4/2006
3/31/2006
5/5/2006

So if I want records for within the next month I get the records with
4/4/2006 and 3/31/2006 in the DateNOver field.

If I want records for within the next two months, I modify the same code a little bit (i.e. instead of -1 and + 1, I replace them with -2 and +2 respectively), and I get the records with 5/5/2006, 4/4/2006 and 3/31/2006 in the DateNOver field.

Thanks again.

ncamoens
Starting Member

9 Posts

Posted - 2006-03-11 : 12:50:16

Honestly, I had no idea of the logic of that piece of code about 10 mins ago, but I certainly do now.

Bye, thanks.

You guys are real angels...shining light on us newbies in the dark.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources