Using Between for DateField storing Text date

[Jigs]

Hi All,

I have a fields "ARRIVAL_DATE" in which arrival date is stored. This fields is text field in which date is stored in DD/MM/YYYY format.

Now, I want to search by ARRIVAL_DATE which falls in between perticular date bounds using between but I am not able to do so. I am not getting the desired result.

Can any body please help me in solving this problem ?

Thanks in Advance,

JIGS

 

[ColinEssex]

Make sure the field is set to the correct date format, then in the query put

 Between [Enter Start Date] and [Enter End Date]

enter the required dates in the correct format.


Col

 

[Bat17]

You can use DateSerial to turn the text dates into numeric dates

myDate: DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2))

HTH

Peter

 

[Jigs]

Still doesn't work

I tried DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2)) but still it doesn't work.

 

[Bat17]

can you post the SQL of what you tried so that we can see what is happening please?

peter

 

[Jigs]

Elaborated Query in ColdFusion

ARRIVAL_DATE is the text field in which date is stored in DD/MM/YYYY format. Now from Coldfusion page, I am using the following query :

<cfquery name="Get_Arrival" datasource="Resv">

SELECT BOOKINGNO, CLIENTNAME, BOOKINGDATE, ARRIVAL_DATE
FROM TRAVELS
WHERE ARRIVAL_DATE BETWEEN #ParseDateTime(DateVar1)# and
#ParseDateTime(DateVar2)#

</cfquery>

where DateVar1 and DateVar2 are 2 Coldfusion Variable in which Dates are stored.

But it doesn't give me the desired result.

 

[Bat17]

Sorry I have never used Coldfusion so I have no idea how it handles dates, but baseing it on Access I would expect something like

SELECT BOOKINGNO, CLIENTNAME, BOOKINGDATE, ARRIVAL_DATE
FROM TRAVELS
WHERE (DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2))) Between #ParseDateTime(DateVar1)# And #ParseDateTime(DateVar2)#

Peter

 

[Jigs]

Datatype mismatch Error

Hi,

the solution which u gave by the following query

SELECT BOOKINGNO, CLIENTNAME, BOOKINGDATE, ARRIVAL_DATE
FROM TRAVELS
WHERE (DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2))) Between #ParseDateTime(DateVar1)# And #ParseDateTime(DateVar2)#

gives the Datatype mismatch Error in both MS-Access and ColdFusion

 

[Bat17]

In Access I would use

WHERE (((DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2))) Between CDate([Start]) And CDate([end])));

I was just guessing at the way cold fussion would handle it though

peter

 

[Jigs]

Hi,

According to your suggestion, I had tried in MS-Access query as following :

SELECT ACH_REF, CLIENTNAME, DATE, ARRIVAL_DATE
FROM BOOKINGS
WHERE (((DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2))) Between CDate([#12/06/2005#]) And CDate([#14/06/2005#])));

When I tried to execute this query, It asks for the Following :

Enter Parameter value : #12/06/2005#

 

[Bat17]

if you had used the sql as posted it would have popped up the box's for you to type the dates in

try
SELECT ACH_REF, CLIENTNAME, DATE, ARRIVAL_DATE
FROM BOOKINGS
WHERE (((DateSerial(Right([ARRIVAL_DATE],4),Mid([ARRIVAL_DATE],4,2),Left([ARRIVAL_DATE],2))) Between #6/12/2005# And #6/14/2005#));

Note that date in SQL are US centric so use MM/DD/YYYY

HTH

Peter

 

[Jigs]

Data Type Mismatch

Hi,

Now it gives "Data Type Mismatch" error.

Jigs

 

[Bat17]

In Access?

 

[Jigs]

It gives "Data Type Mismatch" error in MS-Access

Yes, It gives "Data Type Mismatch" error in MS-Access

Jigs

 

[Jigs]

Even gives the same error "Datatype Mismatch" in ColdFusion too.

Jigs

 

[Bat17]

Works OK when I tested it in Access. Are you sure that ARRIVAL_DATE is a text field?

Can you strip a copy of your DB to the Bookings table, with some non-confidential sample data, and the query you are using and post it here as a Zip file so that I can try to sort it for you.

Peter

 

[Jigs]

Here is the JigsDatabase.zip file which consists of 1 table bookings with required fields.

Can u please have a look over it Peter.

Jigs

 

[Bat17]

The problem is that your dates formats are inconsistant. You have used M/D/YYYY, M/DD/YYYY, MM/DD/YYYY, DD/MM/YYYY.

The SQL would work with the last format, I can code around the variation in formats between 4/23/2005 and 04/23/2005 but I cant make the programme guess whether 06/09/2005 is June or September!

Which data format are you going to use in the production version?

Peter

 

[Jigs]

Hi,

In the Production Version, I am going to use date format DD/MM/YYYY.

The different date formats in the table was becuase the table already had some live data with MM/DD/YYYY date format and When I was handed over this project, the specification changed to DD/MM/YYYY. So in booking engine, I changed the format to DD/MM/YYYY. Thats why it has different formats for data in Arrival_Date. But In the Production version, DD/MM/YYYY date format will be used. If I am changing the datatype of ARRIVAL_DATE field from text to date with format DD/MM/YYYY, then also it is not helping me.

Please Help

Jigs

 

[Bat17]

If you delete out the duff records, say the first 550 then you should find the query will work.

The query does not change the Data Type of ARRIVAL_DATE it just creates a seperate column to allow it to filter by real dates.

Peter