CODEWITHSUNDEEP
javascriptdatedatetimebreeze
user2585299
user2585299

Reputation: 883

Breeze query not fetching records that checks for date columns in sql server table

var shippedFromDt = new Date(2013, 09, 09);
var query = EntityQuery.from('Shipments')
            .where(breeze.Predicate.create("ShipmentDate", ">=", shippedFromDt))
            .orderByDesc("ID")

I have records in table where the Shipment date is '10/9/2013'. But when I run above query, I do not get any records. If I change the date to '10/8/2013', the query returns me the records. I am also using ">=" query operator & so the query should return me the data when the input date is '10/9/2013'. I am not sure if this is happening because the shippedFromDt includes time information also where as the table column only stores date. I tried creating the Date object by passing 0 values for hours,min & seconds parameters but this does not help.

Javascript month index starts from 0, hence 09 is used for October.

Upvotes: 1

Views: 618

Answers (1)

Jay Traband
Jay Traband

Reputation: 17052

This likely a timezone issue. Basically any date you create on the client is defined as being in the client browser's timezone. Dates stored on the database server, by comparison, unless they are of DateTimeOffset or DateTime2 datatypes, are defined without any timezone specified, and thus may be considered as having a UTC Offset of 0. i.e GMT. So your best bet is to adjust your client time by the client timezone offset to convert them into GMT times; something like this. ( I haven't checked the line below, so the sign might be off).

var adjustedDate = new Date(yourDate.getTime() - (yourDate.getTimezoneOffset() * 60000));

A similar problem discussed on SO

Another approach is to define your date as a string with a "Z" specifier to indicate that you want your date to be a UTC O offset and then parse the string into a date.

A final approach is to use DateTime2 or DateTimeOffset datatypes in your database where the timezone offset for all data is the same as that on your client.

Upvotes: 1

Related Questions