none
get different data time when run at different time RRS feed

  • Question

  • I run the script in my VM.

    Code Snippet

    select DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) )

     

     

    In the morning, get

    2008-11-18 00:00:00.000

    In the afternoon

    get

    2008-11-18 12:00:00.000

     

     

    Why?

     

    Thanks

    Wednesday, November 19, 2008 3:48 PM

Answers

  • You declare the variable as datetime, and use the formula I showed you above.

     

    Then if you absolutely have to, convert the value to varchar (but only asfter you've verified the correct result is showing).

     

     

    Thursday, November 20, 2008 8:28 AM
    Answerer

All replies

  • Hi
    Are you sure. Bcoz I run the following queries in my machine and all gave the same results for me

    select DATEADD(d, 0, DATEDIFF(d, 0, '2008-11-19 02:00:00.000'))
    select DATEADD(d, 0, DATEDIFF(d, 0, '2008-11-19 03:00:00.000'))
    select DATEADD(d, 0, DATEDIFF(d, 0, '2008-11-19 12:00:00.000'))
    select DATEADD(d, 0, DATEDIFF(d, 0, '2008-11-19 21:58:58.380'))
    select DATEADD(d, 0, DATEDIFF(d, 0, '2008-11-19 23:59:00.000'))
    Wednesday, November 19, 2008 4:34 PM
  • Jagatheesan

    Have you tried running them at different times of the day... I think Ardmore is suggesting that the same code run in the morning doesn't return the same results in the afternoon.

    Personnally I think datediff is rounding to the nearest half-day... I wouldn't of thought it worked like that, but it does look that way.
    Wednesday, November 19, 2008 4:39 PM

  • Hi Jon,
    I didn't try on different times. But I used hard coded date values with different times. Given the queries in my above post. I used today's date and different times. It is not rounding to nearest date.
    Wednesday, November 19, 2008 4:43 PM
  •  

    your function is incorrect.

     

    Code Snippet

    SELECT DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)

     

     

    Wednesday, November 19, 2008 4:47 PM
    Answerer
  • Hi Gvee,

    SELECT DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) )
    &
    SELECT DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)

    Both gives same result  bcoz in first case implicit conversion happens from number to datetime...
    Any idea why time component gets added for him? I ran in my machine but time component doesn't get added.  

    Wednesday, November 19, 2008 4:56 PM

  • I've run all the code snippets from above and I don't get the time part on any of them...
    I'm running SQL server 2000.

    Wednesday, November 19, 2008 5:17 PM
  •  Jagatheesan wrote:
    Hi Gvee,

    SELECT DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) )
    &
    SELECT DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)

    Both gives same result  bcoz in first case implicit conversion happens from number to datetime...
    Any idea why time component gets added for him? I ran in my machine but time component doesn't get added.  

    Not quite.

    In my examples, yes, the zeroes are getting converted implicitly to the base date.

    My function is equivalent to

    Code Snippet

    SELECT DateAdd(dd, DateDiff(dd, '1900-01-01T00:00:00.000', GetDate()), '1900-01-01T00:00:00.000')

     

    Here, we take the difference in days between the base date at the current date. We then add this number of days back on to the base date, thus truncating the time portion.

     

     

    Whereas, our friends example is

    Code Snippet

    SELECT DATEADD(d, 0, DATEDIFF(d, '1900-01-01T00:00:00.000', GETDATE()) )

     

    Note that the first 0 does not convert, as the 2nd arguement of the DateAdd function accepts integer values only (it's the number of dateparts to add!).

    So in this function, we once again take the difference between the base date and current date but this time we do something entirely different to it.

     

    Basically, they are not identical (I know I've rambled, so ask if it isn't clear)

     

    To the OP: try the function I provided and see how you get on.

    Wednesday, November 19, 2008 5:57 PM
    Answerer
  • This happens when I define

    Code Snippet

    declare @BeginDate varchar(50)
    select @BeginDate = DATEADD(d,  DATEDIFF(d, 0, GETDATE()) ,0)
    select @BeginDate 

     

     

    I would not change it as a timedate type.

    How to handle it?

    Wednesday, November 19, 2008 10:11 PM
  • SQL always includes a TIME value in a datetime (or SmallDateTime) datatype.  If the Time portion is not specified, then SQL assumes that the TIME portion is 00:00:00 (which is 12:00:00AM).

     

    My guess is that your application is shaving off the 'AM' portion of the string and it appears to be 12 Noon when in fact, SQL is saying:

    2008-11-18 12:00:00.000AM

     

    The code you have provided, as is, will never produce any TIME portion other than 12:00:00.000AM (or 00:00:00.000).

     

     

     

     

     

    Thursday, November 20, 2008 1:13 AM
  • So can  you tell how to shave offthe 'AM' portion?

     

    Thursday, November 20, 2008 3:03 AM
  • You declare the variable as datetime, and use the formula I showed you above.

     

    Then if you absolutely have to, convert the value to varchar (but only asfter you've verified the correct result is showing).

     

     

    Thursday, November 20, 2008 8:28 AM
    Answerer