Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

Date format in DRW query

I have a form which displays results based on 2 dates, DateFrom and DateTo.

However the form or server doesn't handle the dates correctly. Despite everthing being set up regionally correctly (UK) and hence dd/mm/yyyy, the dates are taken as mm/dd/yyyy.

How can I correct this so the query handles the dates correctly? The date part of my query is simply..

WHERE (tblDefect.DateClosed BETWEEN #::DateFrom::# And #::DateTo::#)

I've found that, the problem occurs dependant on the date entered.

All dates are entered as dd/mm/yyyy format.

Enter 01/10/2003 - This is taken as 10-January-2003 (should be Oct 1st)
Enter 15/10/2003 - This is taken as 15-Octover-2003 (correct)

Strange or what!

Please help me!  
0
CraigBFG
Asked:
CraigBFG
  • 3
1 Solution
 
hhammashCommented:
Hi,

try this in your sql statement:

Select Field1, Format(DateField,'dd/mm/yyyy/) As FieldAlias from TableName.

Example a table with fields:

Name (text)
DateEntered(date)

Would use this sql to display British date.

SELECT Name, FORMAT (DateEntered,' dd/mm/yyyy' ) As MyDate FROM Dates

Then you can continue with the where statement.

Regards
hhammash

 
0
 
hhammashCommented:
Hi,

The first line that show that format needs this correction:

Select Field1, Format(DateField,'dd/mm/yyyy/) As FieldAlias from TableName

after the yyyy put ' instad of /

Select Field1, Format(DateField,'dd/mm/yyyy') As FieldAlias from TableName


hhammash
0
 
CraigBFGAuthor Commented:
Thanks hhammash but that didn't quite work.
I managed to locate an alternate solution using the function below.

      '       ----------------------------------------------------------------------------
      '      Function to convert dates entered in form to MediumDate format
      '      ASP/DB query natively converts to mm/dd/yyyy regardless. Function
      '      converts dd/mm/yyyy into dd/MM/yyyy. DB accepts and returns correct values
      '       ----------------------------------------------------------------------------
            Function MediumDate (str)
                Dim aDay
                Dim aMonth
                Dim aYear

                aDay = Day(str)
                aMonth = Monthname(Month(str),True)
                aYear = Year(str)
      
                  MediumDate = aDay & "-" & aMonth & "-" & aYear
            End Function
      '       ----------------------------------------------------------------------------

strDateFrom = Request.Form ("DateFrom")
                strfirstDate = MediumDate(strDateFrom)
0
 
hhammashCommented:
Hi Craig,

I thought that you want the query to collect the dates from the database and then displays them on the screen with dd/mm/yyyy format.

Regards
hhammash
0
 
Computer101Commented:
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now