locked
date parameter RRS feed

  • Question

  • User567836715 posted

    Hi,

    I have a date parameter in a stored procedure, it works fine apart from when i want to get a single day it brings back no results when it should:

    i.e. if i put in 13/12/2010 to 13/12/2010 - it wont bring back the results for that day, I can get round this by changing the to date to 14/12/2010, but its not ideal.

    code: WHERE 

    LatestInvoiceVersion.InvoiceDate >= @StartDate and LatestInvoiceVersion.InvoiceDate <= @EndDate

     

     

    Wednesday, December 15, 2010 9:20 AM

Answers

  • User-1460196090 posted

    Hi robert.

    You can convert the LatestInvoiceVersion.InvoiceDate to varchar and get only the date part of it. I think the problem here is because you have the dates in database in the following format:

    Example: 2010-12-15 17:15:57.937

    and you need only the bolded part to compare.

    First of all, why your scenario wasn't working as expected?

    if @StartDate and @EndDate = 15/12/2010

    when comparing

    InvoiceDate >= @StartDate (true)
    InvoiceDate = 15/12/2010 17:15:57.937
    @StartDate = 15/15/2010

    then if you compare the oposite with <= it won't be true since the dates are not the same, the InvoiceDate (I suppose) contains hour:minutes:time.miliseconds in it.

    To run example test, here is code snippet

    declare @start as datetime
    declare @end as datetime
    set @start = '2010-12-15 17:15:57.937'; -- example InvoiceDate
    set @end = '2010-12-15 17:15:57.937'; -- example InvoiceDate
    if (@start>='20101215') print ('start yes')
    if (@end<='20101215') print ('end yes')


    now... what you need to change to make your scenario works is

    convert(varchar(8),LatestInvoiceVersion.InvoiceDate,112) >= convert(varchar(8), @StartDate, 112) AND (
    convert(varchar(8), LatestInvoiceVersion.InvoiceDate, 112) <= convert(varchar(8), @EndDate, 112))


    of course you can remove the conversion from the @StartDate and @EndDate if you are sure you send the dateformat string as it is when its converted.

    Try it and write back your feedback.

    Hope this helps.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 15, 2010 11:38 AM

All replies

  • User-1460196090 posted

    Hi robert.

    You can convert the LatestInvoiceVersion.InvoiceDate to varchar and get only the date part of it. I think the problem here is because you have the dates in database in the following format:

    Example: 2010-12-15 17:15:57.937

    and you need only the bolded part to compare.

    First of all, why your scenario wasn't working as expected?

    if @StartDate and @EndDate = 15/12/2010

    when comparing

    InvoiceDate >= @StartDate (true)
    InvoiceDate = 15/12/2010 17:15:57.937
    @StartDate = 15/15/2010

    then if you compare the oposite with <= it won't be true since the dates are not the same, the InvoiceDate (I suppose) contains hour:minutes:time.miliseconds in it.

    To run example test, here is code snippet

    declare @start as datetime
    declare @end as datetime
    set @start = '2010-12-15 17:15:57.937'; -- example InvoiceDate
    set @end = '2010-12-15 17:15:57.937'; -- example InvoiceDate
    if (@start>='20101215') print ('start yes')
    if (@end<='20101215') print ('end yes')


    now... what you need to change to make your scenario works is

    convert(varchar(8),LatestInvoiceVersion.InvoiceDate,112) >= convert(varchar(8), @StartDate, 112) AND (
    convert(varchar(8), LatestInvoiceVersion.InvoiceDate, 112) <= convert(varchar(8), @EndDate, 112))


    of course you can remove the conversion from the @StartDate and @EndDate if you are sure you send the dateformat string as it is when its converted.

    Try it and write back your feedback.

    Hope this helps.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 15, 2010 11:38 AM
  • User567836715 posted

    cheers mate, that works. 

    Thursday, December 16, 2010 10:19 AM
  • User-1460196090 posted

    cheers mate, that works. 

    Glad to hear that :)!

    Thursday, December 16, 2010 11:06 AM
  • User1471008070 posted

    Hi robert,

    If Hajan's reply helped you, could you please mark it as answer, then other members will know the direction when encounter the same issue. It is also a little award to the member who give the correct reply.

    Thanks,
    Challen Fu

    Monday, December 20, 2010 5:51 AM