locked
Date is different from the saved date on hosted server: SQL RRS feed

  • Question

  • User1052662409 posted

    Hi All,

    I have developed a web application which is working fine. I have tested it on my local machine.

    I have used below query to generate some report where I have used where clause as below.

    ......... WHERE Location=@Location and cast(In_Date_Time AS DATE)=cast(@In_Date_Time AS DATE)

    On hosted server above query gives a different time in report where as on local machine is showing the same time which I have saved in db.

    I am inserting time in db using  DateTime.Now in c#. I also tried with the below query suggested by someone, but no success

    ....WHERE Location=@Location and  DATEADD(MI, 330, In_Date_Time) = DATEADD(MI, 330, @In_Date_Time)

    Please suggest.

    Thursday, April 5, 2018 11:46 AM

Answers

  • User-1716253493 posted

    Maybe you can use GETDATE() or CAST(GETDATE() as Date) in Sql as DateTime.Now

    INSERT INTO TBL (In_Date_Time) VALUES (GETDATE())
    SELECT * FROM TBL WHERE CAST(In_Date_Time AS DATE)=CAST(GETDATE() AS DATE)

    Your code should work if the column is DateTime or Date datatype

    My question is why you add MI 330 in both side? MI 330 become useless

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 6, 2018 1:18 AM

All replies

  • User726159118 posted

    Hi Demoninside9,

    Also please check what is data present in the column. and what is the data you are sending, Also please check below query may be it will help.

    WHERE Location=@Location and CONVERT(date,In_Date_Time) = CONVERT(date,@In_Date_Time) 

    Mark As Answer if it is helpful.

    Regards,


    Thursday, April 5, 2018 12:05 PM
  • User475983607 posted

    On hosted server above query gives a different time in report where as on local machine is showing the same time which I have saved in db.

    The web server is in a different timezone than your local machine.

    Thursday, April 5, 2018 12:07 PM
  • User-1716253493 posted

    Maybe you can use GETDATE() or CAST(GETDATE() as Date) in Sql as DateTime.Now

    INSERT INTO TBL (In_Date_Time) VALUES (GETDATE())
    SELECT * FROM TBL WHERE CAST(In_Date_Time AS DATE)=CAST(GETDATE() AS DATE)

    Your code should work if the column is DateTime or Date datatype

    My question is why you add MI 330 in both side? MI 330 become useless

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 6, 2018 1:18 AM
  • User1052662409 posted

    oned_gk

    SELECT * FROM TBL WHERE CAST(In_Date_Time AS DATE)=CAST(GETDATE() AS DATE)

    Today (on 6th April Friday 07:16 AM) I inserted a records Using DateTime.Now (c#) it is inserting date "2018-04-05 18:47:00"

    According to tour query it is showing the right report with right time "Thursday, April 5, 2018 6:47 PM"  according to the date  inserted into database.

    Please suggest.

    Friday, April 6, 2018 1:52 AM
  • User1052662409 posted

    The web server is in a different timezone than your local machine.

    Sir, I already know this.

    Friday, April 6, 2018 1:52 AM
  • User726159118 posted

    Hi Demoninside,

    Ok got your issue.  if you are able to insert data using stored procedure then use stored procedure. and use get date to function for storing records. may be it will resolve your issue.

    Mark As Answer if it is helpful.

    Regards,

    Friday, April 6, 2018 5:29 AM
  • User-1716253493 posted

    DateTime.Now will get current web server time, getdate() will get current sql server time.

    If you get wrong datetime value when using DateTime.Now, maybe the server has wrong datetime setting.

    Ensure server time setting is correct, date, time, time zone, AM/PM value

    Simple way to check web server time

    Label1.Text = DateTime.Now.ToString()

    Friday, April 6, 2018 7:08 AM