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

  • 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
    Tuesday, June 7, 2011 12:43 PM

Answers

  • 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
    • Proposed as answer by Kent Waldrop Tuesday, June 7, 2011 1:01 PM
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 5:36 PM
    • Unmarked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 7:47 PM
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 7:48 PM
    Tuesday, June 7, 2011 12:46 PM
  • 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
    • Proposed as answer by Kent Waldrop Tuesday, June 7, 2011 1:00 PM
    • Unproposed as answer by Kent Waldrop Tuesday, June 7, 2011 1:00 PM
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 5:36 PM
    Tuesday, June 7, 2011 12:52 PM
  • 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
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 7:48 PM
    Tuesday, June 7, 2011 7:47 PM
  • 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



    • Edited by Naomi N Tuesday, June 7, 2011 8:16 PM Fixed bad formatting from IE9.
    • Proposed as answer by Kent Waldrop Wednesday, June 8, 2011 11:55 AM
    • Marked as answer by Kent Waldrop _ Monday, June 13, 2011 1:16 PM
    Tuesday, June 7, 2011 8:10 PM

All replies

  • 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
    • Proposed as answer by Kent Waldrop Tuesday, June 7, 2011 1:01 PM
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 5:36 PM
    • Unmarked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 7:47 PM
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 7:48 PM
    Tuesday, June 7, 2011 12:46 PM
  • 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
    • Proposed as answer by Kent Waldrop Tuesday, June 7, 2011 1:00 PM
    • Unproposed as answer by Kent Waldrop Tuesday, June 7, 2011 1:00 PM
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 5:36 PM
    Tuesday, June 7, 2011 12:52 PM
  • 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

    Tuesday, June 7, 2011 12:56 PM
  • 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
    Tuesday, June 7, 2011 1:03 PM
  • 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
    • Marked as answer by HESHAM HEMDAN Tuesday, June 7, 2011 7:48 PM
    Tuesday, June 7, 2011 7:47 PM
  • 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?
    Tuesday, June 7, 2011 8:05 PM
  • 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



    • Edited by Naomi N Tuesday, June 7, 2011 8:16 PM Fixed bad formatting from IE9.
    • Proposed as answer by Kent Waldrop Wednesday, June 8, 2011 11:55 AM
    • Marked as answer by Kent Waldrop _ Monday, June 13, 2011 1:16 PM
    Tuesday, June 7, 2011 8:10 PM