0

I am saving my dates in db & using datepicker in fontend & there it is showing the same date that I'm selecting and I've two remote machines, and I'm running the same code on all the machines and with each machine different values of dates are going in the db. For e.g: If I'm selecting date 03/01/2019 (mm/dd/yyyy). Then the dates are saving as :

2019-02-28 18:30:00.0000000 +00:00  = query created from Indian machine 
2019-03-01 00:00:00.0000000 +00:00  = query created from 2nd country machine
2019-02-28 22:00:00.0000000 +00:00   = query created from 3rd country machine

How do I get the selected date i.e: 03/01/2019

ArUn GuPta
  • 23
  • 8
  • Check the time of machines – Syed Mehtab Hassan Mar 14 '19 at 07:19
  • the machines are having right time according to their timezones. – ArUn GuPta Mar 14 '19 at 07:20
  • 1
    can you please explain more, or some code to understand it better – Hamza Ahmed Mar 14 '19 at 07:20
  • I've a datepicker where I'm selecting a date as input and it is saving the date directly to db as the above formats , now when I am trying to read the dates I am getting different dates , instead of 1st of march I'm getting 28th of fab var formattedDate = new Date(d); { formattedDate= (formattedDate.getMonth() + 1 + '/' + formattedDate.getDate() + '/' + formattedDate.getFullYear()); } – ArUn GuPta Mar 14 '19 at 07:25
  • @Fakenick I understand that , but now I want all of them to return me the same date , I tried all the options I'm not getting 03/03/2019 for all the three timezones . – ArUn GuPta Mar 14 '19 at 07:31
  • Does timestamp matters for you? – Pal Singh Mar 14 '19 at 07:49
  • @PalSingh no Timestamp is of no use only dates matter to me. – ArUn GuPta Mar 14 '19 at 09:22

2 Answers2

0

You should consider storing date time in UTC format so you can convert it back to users timezone. If you are only interested in storing Date, then no need to convert it to UTC and only store the date, not time. I would recommend you to read this article to know your requirement and use the solution which works for you.

Pal Singh
  • 1,964
  • 1
  • 14
  • 29
  • @ArUn, If you don't need timestamp, don't save it. Just save the date as it is. It will be a lot easier. – Pal Singh Mar 15 '19 at 00:58
0

This is a thorny and unavoidable problem ! As you say, you're already in trouble because timezone has already crept in. Dates should always be stored as UTC midnight at the start of the date in question, and the only way you can get there now is to round to the nearest midnight. A day has 86,400,000 milliseconds. In javascript, round your dates like this...

var ticksAsDays = new Date('2019-02-28 22:00:00.0000000 +00:00').getTime() / 86400000;
var midnightUTC = Math.round(ticksAsDays) * 86400000;
var asADateObject = new Date(midnightUTC);

Going forward, pay super attention if you're handling just-a-date that no notion of time or timezone creeps in, that your just-a-date is a midnight UTC moment. Much more info here.

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110
  • but while rounding off , I'm loosing one day in some cases & that's a big issue . – ArUn GuPta Mar 14 '19 at 18:15
  • No. We used round (to nearest), not floor. There are some Island in the Pacific that have a utc+13 timezone, but apart from that you should be ok. – bbsimonbb Mar 14 '19 at 18:20
  • Test with all your data. The example works with your 3 dates in the question, that fall both sides of midnight. Something somewhere is creating dates with local midnight. You can also round directly in SQL, in the DB. Try (test first) `update myTable set myDate = convert(date, myDate + 0.5)` – bbsimonbb Mar 15 '19 at 07:55
  • thanks buddy , it's working smoothly . 98%of my data is working fine :D – ArUn GuPta Mar 18 '19 at 12:04