Solved

Date format in DRW query

Posted on 2003-11-06
6
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

635 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