Solved

Converting date to number

Posted on 2002-04-25
13
329 Views
Last Modified: 2012-06-27
I have been told that the best way to store dates in an Access DB is to convert the date to a number , write it and then when the date is required find it and convert it back to whatever date format is on the machine and display it.
What is your opinions on this and if you think that this is a good practice perhaps you might have some code to demonstrate it.

Hope to hear from you experts

Edel
0
Comment
Question by:edel2000ie
  • 6
  • 2
  • 2
  • +2
13 Comments
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
That will work, though it can be frustrating if you have different methods of accessing the data as you will have to cope with this numeric (double data type) storage in each application. Personally using access I always ensure that I write dates to it using "yyyy-mm-dd" as this ensures that the date is converted when stored to the correct date format, when reading it that is fine too. The date is probably stored internally as a double rather than as a string so using this method preserves the simple date handling functions and eliminates the need to code around storing it as a number yourself.
0
 
LVL 1

Expert Comment

by:gron
Comment Utility
It depends on the application and what you're using the date for. If it is just for display purposes, then the above sounds reasonable. But in many business applications you have to do a lot of calculations on dates, and it is often easier to establish a standard date format and stick with it. I like yyyy-mm-dd because it sorts into chronoligical order.
Depending on the application again, you can reset the workstation's short date format as follows:

' Reset system short date format to yyyy-MM-dd
newFormat = "yyyy-MM-dd"
Call SetLocaleInfo(LCID, LOCALE_SSHORTDATE, newFormat)

This is acceptable in a company environment, but may not be for the general public.
0
 

Author Comment

by:edel2000ie
Comment Utility
thanks gron and TimCottee Yes there will be a lot of date calculations. The generally accepted date format in most companies here is dd/mm/yyyy. So if I develop my app in this date format and coerce the date format of the client PC to this date then I should have no problem. I think that setting the date will be acceptable to the companies that the app will be developed for. They will be informed before they buy the product.

If anyone one has more ideas please let me know

Edel
0
 
LVL 10

Expert Comment

by:smegghead
Comment Utility
If you are planning on reporting from your database using a 3rd party reporting tool, such as crystal reports, all of these fields will be treated as numerics. You will lose the ability to format the displayed date (although you can do this using formulas). You will also not be able to group things by month or year etc..

I personally prefer using the date data type, it may take up slightly more space, but in the long run, it's more consistant.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 50 total points
Comment Utility
edel2000ie, dd/mm/yyyy is fine except that you should ALWAYS coerce the date into the yyyy-mm-dd format when inserting/updating or when using a date as a query parameter. Access has this annoying habit of treating all dates used as query values in mm/dd/yy regardless of the locale setting of the machine. Therefore a query such as

Select * From MyTable Where StartDate >= #02/05/2002#

May well be interpreted wrongly depending on the date settings of the machine, access will always read this as Feb 5th rather than 2nd May.

Select * From MyTable Where StartDate >= #2002-05-02#

Will always be treated as may 2nd. You can do this in VB using the format command to simply transform the date into the appropriate format:

"Select * From MyTable Where StartDate >= #" & Format(MyDateVar,"yyyy-mm-dd") & "#"

0
 

Author Comment

by:edel2000ie
Comment Utility
Smegghead
Yes I do intend to use Cryatal Reports. Good point. Thank you.

TimCottee
Do you format each date before you write it to the database and then format it again when you read it.
e.g
rstRecSet!StartDate = format(DTPicker1.value,"yyyy-mm-dd" )

Edel
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:edel2000ie
Comment Utility
sorry TimCottee got our messages crossed
0
 

Author Comment

by:edel2000ie
Comment Utility
Tim Cottee

OK TimCottee now we are getting somewhere.
So let me run through a senario that I think should be safe from what you have told me.

Before I write a date to Access format it to yyyy-mm-dd.

Before I display a date I format it to what ever the user wants, in my case dd/mm/yyyy.

If the user selects a date in a DTPicker for a query, I format it to yyyy-mm-dd before I put it in the SQL statement.

I select a short date format for my Access date fields.

It does not matter what date locale settings are on the user PC. If I open my database on the users machine and they have date settings of dd-mm-yyyy, will I see the date in format dd-mm-yyyy or yyyy-mm-dd? Does it matter if the date is stored as a double. Is writing it in yyyy-mm-dd format just making sure that Access is able to convert it properly to a double?

Have I got it right?

If this is the way to go then I am off to write an app with confidence. In the past I have had a few problems with dates in Access. I worked around them but this app is pretty big so I want it dead right.

Your help is much appreciated

Edel




0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Since you plan to use Crystal Reports, knowing the format of dates is important.  CR expects dates to be in a specific format (yyyy-mm-dd).

If the users are going to be entering dates, I suggest you use a calendar dropdown otherwise the machine locale format may interfere and also users will enter dates in different formats.  We have an Access application used by 5-600 people where dates are critical.  If a user has changed his local format the program may not allow him to enter a date. We use dd/mmm/yyyy for entry but an occasional user will change his local format to be dd-mmm-yyyy and the date can't be entered until we change him back.

In our recent rewrite (not yet installed) we chose to use a Sheridan calendar control and not to allow the user to type the date in simply to prevent these problems.

good luck
mlmcc
0
 

Author Comment

by:edel2000ie
Comment Utility
Hi mlmcc
I am using a DateTime Picker to select the dates. The format I am using for this is dd/mm/yyyy. When the user selects a date I will convert it to yyyy-mm-dd and write it to DB or add it to SQL statement.

If I keep all dates in the DB as yyyy-mm-dd then I will be fine with CR, is this right?

Can you see any more problems that I may have?

Edel
0
 
LVL 1

Expert Comment

by:gron
Comment Utility
My app has 75 forms and about 35 CR reports, many with dates, and I have had no problems with yyyy-mm-dd. Another alternative for date entry is the Masked Edit Box. In many cases, for example when the year and date are predetermined and all the user has to do is enter the day, you can enter the year-month and position the cursor at the day.
What I meant earlier when I said that this formats sorts in chronological order, I should have specified that even as text or string format it sorts correctly. The other date formats don't.
0
 
LVL 10

Expert Comment

by:smegghead
Comment Utility
just looking back on your question...

is your freind suggesting that you convert the date to a numeric, and write it to an integer/long database field ??

If so, CR will always treat this as a numeric (unless you write a formula to convert it)

If your database uses date fields, then CR will treat the field correcly regardless of what format you pass in.. as long as it's valid.

PS. why does your friend suggest converting to a number ?? just for the sake of saving a few bytes ???
0
 

Author Comment

by:edel2000ie
Comment Utility
smegghead
I think the philosophy behind my friend's idea of using a long field to hold dates was that they could not be interferred with regardless of locale settings or Access querks. The CR approach was not considered in depth.

gron
this masked edit approach is a very good idea. I think I will try the DateTimePicker first and see how it goes.

Well Thanks everyone, you have helped me tremendously.

Edel
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

10 Experts available now in Live!

Get 1:1 Help Now