The best way to handle dates and times in access or sql

[gsc123]

Hi

What is the best way to handle dates and times in access or sql? seperate columns

days hours minutes etc.

or one column date/time because I'm having all sorts of errors due to the way access stores them

 

[PHV]

I'd use a single DateTime column.
Which errors do you get doing what ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gsc123]

Well, which would you say is the least date?

pStartDate- 7/2/2009 21:15:50
pEndDate- 6/13/2009 23:56:04

I need the format dd/mm/yyy - time on both

If i use in asp

if pStartDate < pEndDate then
carry on...

but no I get the else evaluation from this

 

[PHV]

Seems like you use String instead of Date variables.
A DateTime variable is in fact a floating point value:
the integral part is the date
the decimal part is the time

What about this ?
If CDate(pStartDate) < CDate(pEndDate) Then


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

As PHV says, Cdate will work for this example. It doesn't account for why you're trying to use one date in dd/mm/yyyy format and one in mm/dd/yyyy format?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[gsc123]

asp function Now() as a different format to how access db stores it so i cant evaluate time left... Whats the best way to handle bomb proof wise?

 

[PHV]

asp function Now() as a different format to how access db stores it
????

 

[gsc123]

Well, if cdate works, what about time factor on the same dates?

 

[MajP]

In vb as mentioned a date is stored as a floating point with a integer part representing how many days have elapsed since the base date of 12/30/1899. The decimal part is fraction of time since midnight. Every time you look at a date it has a format applied if not your date of

"6/13/2009 23:56:04"
would appear as 39977.9972685185

which is 39,977 days since 12/30/1899
and .9972685185 of a day or (23 hrs, 56 min and 4 sec)

So if you enter into a date field just 6/13/2009 it is stored as the value 39977.0
or if you enter the value 23:56:04 it is stored as 0.9972685185.

So here is a test
[/code]
Public Sub test4()
Dim strDate As String
Dim dtm As Date
Dim dblDate As Double
strDate = "6/13/2009 23:56:04"
dtm = CDate(strDate)
dblDate = dtm
Debug.Print dblDate
Debug.Print Format(dtm, "mm/dd/yyyy")
Debug.Print Format(dtm, "hh:nn:ss")
Debug.Print Format(dtm, "mmmm, dd, yyyy")
Debug.Print Format(dtm, "q\Quarter yy")
End Sub
[/code]
and your output

39977.9972685185
06/13/2009
23:56:04
June, 13, 2009
2Quarter 09

Which shows how your date is actually stored, and that you can format it almost any way you want for display purposes.

 

[gsc123]

So from your example how would I test for startdate endDate from the db?

 

[MajP]

I thought PHV answered that already.
CDate(pStartDate) < CDate(pEndDate)

Does that not work?

If you are only worried about the date portion. For example to find out if they are on the same date
int(CDate(pStartDate)) = int(CDate(pEndDate))
so in the above if your dates were

"6/13/2009 10:00:00"
and
"6/13/2009 23:56:04"

The above would resolve to
39977.0 = 39977.0 and return true.

But now I am wondering if we are off track. Your original post

pStartDate- 7/2/2009 21:15:50
pEndDate- 6/13/2009 23:56:04

if pStartDate < pEndDate then
carry on...
but no I get the else evaluation from this

Unless you meant something different, you should get the else. The StartDate is not less than the End Date.

 

[jrbarnett]

gsc123

I would also ensure that when writing data into your database, you store it in the ANSI date/time format:

yyyy-mm-dd hh:mm:ss

rather than anything else - use client side formatting in forms/reports to ensure that this is presented as you wish. Without it, I have found that the database gets confused over different date systems across the world.
Eg in the USA where the date system is mm/dd/yyyy: 06/01/2009 is the 1st June 2009.
However in the UK the format is dd/mm/yyyy so the same date is interpreted as 6th January 2009.

John

 

[gsc123]

Ok, we are getting somewhere, do you mean using the function now() is not suitable to the database?

 

[jrbarnett]

No - just ensure it is formatted thus:

Format (Now(), "yyyy-mm-dd hh:mm:ss")

John

 

[gsc123]

How would that appear in the browser as a date?

 

[jrbarnett]

As retrieved from the database it would be something like:

2009-06-16 08:51:05

You can format it using standard functions though to your own requirements.

John