Solved

Date format in DRW query

Posted on 2003-11-06
6
346 Views
Last Modified: 2013-12-24
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
Comment
Question by:CraigBFG
  • 3
6 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 9702666
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
 
LVL 14

Expert Comment

by:hhammash
ID: 9702674
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
 

Author Comment

by:CraigBFG
ID: 9722988
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
 
LVL 14

Expert Comment

by:hhammash
ID: 9723507
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9754127
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now