DATE FORMAT

[jazz007]

Is there a query I can run which will show any records in table that dont meet the date format because of human error ie mistype the format is required is dd/mm/yyyy. sometimes the users type dd/mm/yyy and it messes up export.

Is there any way to have a query on the dates to show any which are not valid ie not in that format. Any help would be much appreciated. Thanks

 

[AlexMidd]

You might be able to - I haven't reied it. Best way is to try it yourself.

Alternatively, would it no be better to have verification at source, i.e. at the time when the user inputs the date?

Have fun! )

Alex Middleton

 

[edsuk]

The best way to do this is to use the 'Left','Mid' and 'Right' functions. For example to check that the 3rd character is '/':

Mid(

![Column],3,1))<>"/"

This will list all where the 3rd character is not /.

You can query each character in the column using these functions:

Left(

![Column],2)>31 (All dd above 31 etc)

Hope this helps.

Mark...

[Worm]

 

[jrbarnett]

Use the following query, substitute your table name and date field name:

select datefield
From Tablename
Where IsDate (Datefield) = False

This will let you update the dates prior to export, be sure to add extra columns into the select list that you need to identify the records. Note that IsDate uses the user country settings to determine if a date is valid or not.

John

 

[AlexMidd]

jazz007,

Do you want to exclude records that do not fit the criteria or find them so that you can change them to the correct format?

I still feel that soem sort of checking at the input end is a better way fo forcing the data to be entered correctly, but I may have misunderstood your requirements.

Have fun! )

Alex Middleton

 

[edsuk]

Alex

I totally agree that data should be checked during input, but from the question it would appear too late for this, hence my suggestion, assuming the field was a text field without date formatting constraint.

Mark...

 

[AlexMidd]

No problem Mark. That was why I asked the question as I realised that it was not absolutely clear from the original question. I would apply your check to check, and update, existing data, and double this with at-source checking and verification to ensure that future data is correct also.

Have fun! )

Alex Middleton

 

[PHV]

jazz007, what is the data type of your date field ?
Hopefully DateTime ...
SELECT * FROM yourTable WHERE Year(yourDateField)<1900

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazz007]

I want to find the incorrect dates so that I can change them to the correct format.

 

[jrbarnett]

In that case, just run my query from my post of 28th July, substituting your table and field names.

John

 

[PHV]

John, a field formatted as dd/mm/yyy IS a date ...
jazz007, you haven't answered my question.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886