MS Access Date Format and Compare yields wrong result

ms access compare dates
ms access format datetime in query
ms access query between two dates
ms access date functions in query
access-vba format date/time
access date functions examples
ms-access vba compare dates
format date field in access report
SELECT Format('06-12-2018','dd-MM-yyyy') > Format('31-07-2018','dd-MM-yyyy')

why i am getting output as '0' meaning false, while running this query on ms access ?

As dd-mm-yyyy is your local format, use DateValue:

SELECT DateValue('06-12-2018') > DateValue('31-07-2018')

or, if the values are hardcoded, though this makes little sense, simply:

SELECT #2018/12/06# > #2018/07/31#

Store, calculate, and compare Date/Time data, Store Date/Time data; Format a Date/Time field; Calculate time data The following table shows how Access stores Date/Time values: you may receive incorrect formatting results when you calculate time intervals greater  I have a problem with Access 2013. I am doing a query to get the records that have dates smaller than the date included in a form. But, I'm getting wrong results. I don't know if that is an Access'

Try this:

SELECT cdate(#06-12-2018#) > cdate(#31-07-2018#)

Change dates by using functions and operators, This article applies to either a Microsoft Access database (.mdb) file or to a Microsoft the calculation functions, and the comparison operators that are available in Access. The Date() function returns the current date in the short-​date format. The result is that the month parameter is evaluated to 2. dbo_Order.ScheduleDate >= Date() AND dbo_Order.ScheduleDate < Date() + 1 DateValue([ScheduleDate])has to be computed on every row and can't use an index (if any) what can affect query performance on huge data. See Access query won't work when dates have times In addition DateValue can't handle NULL values.

for datediff i think you need below type function example

SELECT DateDiff("yyyy", #13/01/1998#, #09/05/2017#); 

Date and time conditions causing SQL performance problems, SQL queries using date and time conditions are easy to get wrong. The Oracle database is particularly vulnerable in this respect because it has only one you can use the TRUNC function on both sides of the comparison—e.g., to search The query uses a date format that only contains year and month: again, this is an​  The Microsoft Access Format function takes a date expression and returns it as a formatted string. The syntax for the Format function in MS Access is: Format ( expression, [ format, [ firstdayofweek, [firstweekofyear] ] ] ) Parameters or Arguments. The value to format. Optional. It is the format to apply to the expression. You can either define

Top 5 Reasons for Wrong Results in SQL Server, Top 5 Reasons for Wrong Results in SQL Server a string representation at any layer, use a culture invariant format like yyyymmdd Moral: Always use an open-​ended range for date range queries, even when using the date data type. the reader think more about the difference between 'Null' and 'zero'. In a form or report. Open the form or report Layout View or Design View. Position the pointer in the text box with the date and time. Press F4 to display the Property Sheet. Set the Format property to one of the predefined date formats.

Format a date and time field - Access - office support, When you use the Date/Time data type in Microsoft Access, you can keep the default Input masks can be applied to fields in tables, query result sets, and to  The Format function returns a string expression, but the data type of the column remains unchanged as date/time. The date/time data type in Access is implemented as a 64 bit floating point number in fact, of which the integer part represents the days, and the fractional part the time of day.

Excel Date and Time, Tip: format the date serial number column as a Date and you'll see they look the Entering the h:mm components will result in a time formatted in military time e.g. The difference between the two date systems is 1,462 days. For example Entering “4/28” yields a date for April 1, 2028, not April 28th of this  Returns items with dates during the last week. A week in Access starts on Sunday and ends on Saturday. Contain dates within the following week. Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1 . Returns items with dates during next week. A week in Access starts on Sunday and ends on Saturday.

Comments
  • Format() returns a string so you are performing a string comparison & the first character '0' is not greater than '3'. Compare date types via cdate() or #06-12-2018# (in your localized format)
  • @AlexK: There are no localised formats for the #..# syntax. In VBA, the syntax is #mm/dd/yyyy#, ADO it is #yyyy/mm/dd#.
  • Datevalue also working .. but i want to format the date before comparing the dates. so i have used.. SELECT cdate(Format('06-12-2018','dd-MM-yyyy')) > cdate(Format('31-07-2018','dd-MM-yyyy'))
  • But that makes no sense. Date values have no format, so you are casting the string first to a date value for Format to read it, then format it to a string, which CDate then converts to a date value using your Windows locale.
  • The format & cdate are unnecessary, ## already denotes a date literal, your going from date to string then back to the same date.
  • SELECT cdate(Format('06-12-2018','dd-MM-yyyy')) > cdate(Format('31-07-2018','dd-MM-yyyy')) - this one working