CODEWITHSUNDEEP
javascriptnode.js
Danielle
Danielle

Reputation: 1496

Date field from SQL returning date minus 1 with Node/JS

I have a very simple SQL string that brings a Date field from sql:

SELECT dbo.table.effDate from table where.....

I need to convert that date to a date variable at Node so I can perform a calculation. This date is always the first of a month, so my results at SQL are

2023-01-01
2022-05-01
2022-08-01

etc.

So, when I try to convert the field to a node date type, in these different ways, I always get the previous date of the one at the field (for the example above)

2022-12-31
2022-04-30
2022-07-31

Im using:

let effDate = moment(response[i].effDate, 'YYYY-MM-DD').format('YYYY-MM-DD')

Then

let effDate = new Date(response[i].effDate)

even this is bringing me a previous date

let effDate = response[I].effDate.toString()

and this:

let effDate = new Date(response[I].effDate).toLocaleDateString('en-US')

I could fix this by adding one day to whatever SQL brings me back, but its not the point, I want to do it right.

I also tried using getTimezoneOffset

effDate = new Date(response[i].effdate.getTime() - response[i].effdate.getTimezoneOffset() * 60000)

But I always get the previous date.

What confuses me is that javascript UTC zones should have nothing to do when it comes to the value returned by SQL:

enter image description here

Again, I could just add a day but that wont be right, and I would like to know why this is happening (even using toString()).

Thanks.

Upvotes: 1

Views: 76

Answers (1)

Arleigh Hix
Arleigh Hix

Reputation: 10897

Basically Date treats strings as being the UTC representation of that moment in time (because its the standard format to save a date in a database). Running the following line should give you a better understanding of this:

console.log(new Date('2023-01-01').toString())

I get:

Sat Dec 31 2022 16:00:00 GMT-0800 (Pacific Standard Time)

Either allow moment.js to interpret the string directly, or use the .utc() method.

// moment from Date object is still UTC, outputs local
console.log('A:', moment(new Date('2023-01-01')).format('YYYY-MM-DD'))
// moment parses string assumming local
console.log('B:', moment('2023-01-01').format('YYYY-MM-DD'))
// use moment.utc() method to keep output UTC
console.log('C:', moment(new Date('2023-01-01')).utc().format('YYYY-MM-DD'))
console.log('D:', moment.utc(new Date('2023-01-01')).format('YYYY-MM-DD'))
console.log('E:', moment.utc('2023-01-01').format('YYYY-MM-DD'))
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.4/moment.min.js"></script>

Upvotes: 1

Related Questions