SQL Date Queries

Results 1 to 4 of 4

Thread: SQL Date Queries

  1. #1
    Peter Goodman Guest

    Default SQL Date Queries

    I have an application that runs an SQL statement to retreive records on a certain date.<BR>It all worked fine until the turn of 2001 . Since then the dates usually in the format dd/mm/yyyy have to be entered into the SQL statement as mm/dd/yyyy . The SQL statement still works for dates in 2000 but anything since01/01/2001 has to be entered with the month first.<BR>New records are date stamped by the server but the server date shows correctly and regional settings are ok.<BR>When I view the database all records are in the correct format.<BR><BR>Please Help!

  2. #2
    peterjl@austec.net.au Guest

    Default RE: SQL Date Queries

    You need to use the CDate Function inconjunction with LCID <BR><BR>Below is from the MSDN about LCID:<BR><BR>@LCID<BR>You can use the @LCID directive to set the locale identifier (LCID) for a script. The LCID is a DWORD containing the language identifier in the lower word and a reserved value in the upper word. The identifier supplied in an LCID is a standard international numeric abbreviation. This LCID has the components necessary to uniquely identify one of the installed system-defined locales. There are two predefined LCID values. LOCALE_SYSTEM_DEFAULT is the system default locale, and LOCALE_USER_DEFAULT is the current user&#039s locale.<BR><BR>Syntax<BR>&#060;%@ LCID=localeidentifier %&#062;<BR> <BR>Parameters<BR>localeidentifer <BR>A valid locale identifier. <BR>See Also<BR>Session.LCID <BR><BR> <BR>Having set the LCID as above, the cdate function will correctly interpret the date properly

  3. #3
    Join Date
    Dec 1969

    Default RE: SQL Date Queries

    Hi Peter,<BR><BR>Lots of people seem to be having this problem. My pages worked at the end of last year but now they are broken. I used as suggested the locale identifier (LCID) script, but found this made no difference. I think the moment you put a date into an SQL statement it is converted to american format even if you have dates in english format on the server, script and database.<BR><BR>You say your date worked for 2000, did it work for 09/01/2000? I would be surprised if it did?<BR><BR>The only way I could get around this was to set up a query in my access database that selected records by date. I wanted to search for dates less than today and it seems to have done the job.<BR><BR>Keep an eye on my earlier post, someone said they would reply with some code :)<BR><BR>http://www.aspmessageboard.com/forum/databases.asp?M=137571&P=1&F=21

  4. #4
    Join Date
    Dec 1969

    Default RE: SQL Date Queries

    why not just shunt the date around manually?<BR><BR>i use the following code to re-work dates. this way i *know* it will work with whatever page i create<BR><BR>SampleDate = NOW()<BR>DateDay = DAY(SampleDate)<BR>DateMonth = MONTH(SampleDate)<BR>DateYear = YEAR(SampleDate)<BR>‘ add a leading zero where necessary<BR>IF LEN(DateDay) = 1 THEN DateDay = ”0” & DateDay<BR>IF LEN(DateMonth) = 1 THEN DateMonth = ”0” & DateMonth<BR>‘ uk style date dd/mm/yyyy<BR>UKDate = DateDay & ”/” & DateMonth & ”/” & DateYear<BR>‘ us style date mm/dd/yyyy<BR>USDate = DateMonth & ”/” & DateDay & ”/” & DateYear<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts