0

I am working in a classic asp project where I am selecting an SQL query from the database. The query was working fine in my own laptop and the date format is '11/14/2018' from this query but it's giving the mentioned error in office system and when I checked the query in SSMS then it's generating the date format as '14-11-2018'. I tried changing the different VB date formats but the result was same.

Here is the query:

 sql="select count(*) as total from hc_query a, hc_breakup b where a.querytype='hotel' and a.qdate='" & FormatDateTime(Now(),vbShortDate) & "' and a.t_id=b.pnrno and b.bookstatus='half'" '

Please give some suggestions. Thanks

Ashutosh Singh
  • 27
  • 5
  • 2
    your surprised that when you pass a date as string in the wrong format it doesn't work? Use a date parameter. – Mitch Wheat Nov 14 '18 at 06:38
  • 1
    Slightly off topic - but you SHOULD NOT be using old-style joins via the where clause. EVOLVE! I would also argue that using single character table aliases are sloppy and lazy coding. – SMor Nov 14 '18 at 13:18

1 Answers1

1

The problem is not in the code but in the assumptions about the function's output. The documentation for FormatDateTime states about the NamedFormat argument:

vbShortDate : 2

Display a date using the short date format specified in your computer's regional settings.

That is, the output format depends on local configuration of the computer running the code. Two different computers can generate different output depending on locale configuration.

If you need a consistent output to deal with dates stored as strings in database fields, then you will need to write your own function to ensure consistency.

MC ND
  • 69,615
  • 8
  • 84
  • 126
  • sir I tried changing the date format of my new system to 'mm/dd/yyyy' but still having the same error. – Ashutosh Singh Nov 14 '18 at 12:29
  • 2
    @AshutoshSingh, you should not depend on locale configuration (by the way, the locale configuration used is the one associated to the user under whose credentials the code is executed). If you have a consistent format requirement then change the code that builds the string and don't depend on system configuration. – MC ND Nov 14 '18 at 13:00
  • thank you, sir, for your valuable time and support. I got some points cleared and further, I want to ask if it's a good idea to change the date format using '.ToString("date format")' as I have read about this in some google research. – Ashutosh Singh Nov 16 '18 at 05:45
  • 1
    @AshutoshSingh, As already indicated in another comments, there are several things you should change (no string stored dates, no concatenation queries, please use stored procedures, ...) but you can not use `.ToString` just because it does not exist in VBScript. It is part of the `DateTime` class in .Net – MC ND Nov 16 '18 at 06:44
  • Ok sir, all doubts cleared. Thanks for the support. – Ashutosh Singh Nov 16 '18 at 10:18