how can i return rows from table by using DATEPART() , please need help

Question

here is my scenario

i have table include (ProductName , ExpireDate)

how can i return value like in WHERE clause by comparing GetDate() that return today Date , and extract day and month and year and compare to return the Expiredate values that equal to the GetDate() Value

i understand how we can extract year , month , day by DATEPART() function

 

 

---

Hesham Hemdan Saleh

Answer 1

Hello Hesham,

Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

MSDN Transact SQL Forum Posting Guidelines:

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

      Posted by Clifford Dibble

      http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

      Posted by Phil Brammer

Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.

EDIT:

Please provide more details about what you are trying to do.  Give a look at the MONTH, YEAR and DAY functions in books online.  Also, queries will be easier to tune if you avoid using where clauses that enclose columns in functions.  In this specific case it might be best if you use inequalties with date ranges for your where clause rather than equalities that are based on functions of a date/time column.  By the way, which version of SQL Server are you using?

If you are using SQL Server 2008 then you and you want to match a particular date you can do something like this:

WHERE expireDate >= convert(date, getdate() and expireDate < convert(date, dateadd(day, 1, getdate()) or maybe even

WHERE expireDate = convert(date, getdate()

 

WHERE expireDate >= convert(datetime, convert(varchar(8), expireDate, 112))
  AND expireDate <  dateadd(day, 1, convert(datetime, convert(varchar(8), expireDate, 112)))

Here is an example of the DAY, MONTH and YEAR built in functions:

select
 month(getdate()) [Month()],
 year(getdate()) [Year()],
 day(getdate()) [Day()]

/* -------- Output: --------
Month()   Year()   Day()
----------- ----------- -----------
6      2011    7
*/

.

otherwise you can do somesimilar to

Answer 2

Declare @Table Table
(
	ProductName varchar(100),ExpiryDate DateTime
)
insert into @Table
Select 'Product1','10/01/2015' UNION ALL
Select 'Product2','10/01/2014' UNION ALL
Select 'Product3','10/01/2013' UNION ALL
Select 'Product4','10/01/2012' UNION ALL
Select 'Product5','10/01/2011' UNION ALL
Select 'Product6',GetDate() UNION ALL
Select 'Product7','10/01/2010'


--get list of products which are expired today
Select ProductName,ExpiryDate,
datepart(year,ExpiryDate),
datepart(month,ExpiryDate),
datepart(day,ExpiryDate)
From @Table
where 
--remove time part
--compare only date
dateadd(dd,datediff(d,0,ExpiryDate),0)=dateadd(dd,datediff(d,0,GetDate()),0)


--get list of products which was expired
Select ProductName,ExpiryDate,
datepart(year,ExpiryDate),
datepart(month,ExpiryDate),
datepart(day,ExpiryDate)
From @Table
where 
--remove time part
--compare only date
dateadd(dd,datediff(d,0,ExpiryDate),0)<dateadd(dd,datediff(d,0,GetDate()),0)

---

Yogesh Bhadauriya
My Blog

Answer 3

oh yogesh , thats was so fast , i just understanding your solution , i hope its work , i will give you mark , when test it , ok , thank you so much

can you just explain for me the dateadd , what is all of this options values  

---

Hesham Hemdan Saleh

Answer 4

One more try.

I don't suggest using this solution. Please read this blog post to understand why

Bad habits to kick : mis-handling date / range queries

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

I think this is what i'm trying to do

SELECT * FROM EXPRESS.dbo.ProductExpireTable WHERE ProductPreExpiringDate BETWEEN  GETDATE() AND DATEADD(MM,2,GETDATE())

 

---

Hesham Hemdan Saleh

Answer 6

I think this is what i'm trying to do

SELECT * FROM EXPRESS.dbo.ProductExpireTable WHERE ProductPreExpiringDate BETWEEN GETDATE() AND DATEADD(MM,2,GETDATE())

 

---

Hesham Hemdan Saleh

The BETWEEN operator for the date/time datatype is slightly shakey.  Again, which version of SQL Server are you using?  You are better off sticking to two inequanlty operators, such as ">=" and "<", rather than use the BETWEEN operator.  Also, is the "productPreExpiringDate" column a datetime datatype?

Answer 7

Not exactly. GETDATE() function stores the date and time portion. You need to strip time portion from the date first.

In SQL Server 2008 you can use

select * from ... WHERE DateField 

between CAST(CURRENT_TIMESTAMP AS DATE) and CAST(DATEADD(month,2,CURRENT_TIMESTAMP) AS DATE)

I suggest to check the blog I mentioned in my prior post

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog