| Author |
 Topic  |
|
|
SaiyanJin
Junior Member
115 Posts |
 Posted - 28 January 2002 : 14:37:39
|
Can anyone clarify to me why as to "The dates on this forum is stored as TEXT and not as DATE/TIME Datatype?"
Correct me if I'm wrong but I think that if all the dates were to be stored as DATE/TIME, it would be so much easier to use. It eliminates the need to convert it back/forth.
Why couldn't the developers just use the DateAdd function?
Please clarify. THanks.
TX
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 28 January 2002 : 15:45:30
|
Dates were stored in text format to allow for us to use one common set of SQL instead of having one that has for # in dates for Access and doesn't for non-access databases...
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
 |
|
|
SaiyanJin
Junior Member
115 Posts |
Posted - 28 January 2002 : 16:05:36
|
Would it be hard to convert it to DATE/TIME datatype?
It should work for SQL as well... to my knowledge.
|
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 28 January 2002 : 20:23:22
|
I'll try this again. The date fields are text to allow us to use ONE set of queries. In access, any query on a date must be surrounded by the # sign. In ALL other types of databases, the # is not used. SO, if the date fields were date/time fields, every place the date is queried on, we would need two queries, one for access, one for the rest. Double work, not good.
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 29 January 2002 : 05:13:18
|
plus you would have to format the date correcty before inserting, so you would need to know the date format of the database before you could post any dattime types.
|
|
|
|
SaiyanJin
Junior Member
115 Posts |
Posted - 29 January 2002 : 11:32:29
|
OK. That's clear for me.
Thanks guys. 'preciate it.
|
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 06 February 2002 : 17:38:24
|
also, it is important to point out that forward looking, Oracle uses a completely different formating for dates from MS SQL Server that uses a completely different formating from MS Access wich is slightly different from MySQL :)
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 14 March 2002 : 06:54:18
|
Isn't ISO ("YYYY/MM/DD TIME") compatible with pretty much all formats? If not, why not store it as a numerical value in this format.. easy to decipher, sort on, switch between DB's.. etc.
All we then need is a reusable input and output function to handle all conversions.
This is quite a nifty output function: http://www.4guysfromrolla.com/webtech/022701-1.shtml
PS. Myself and Ruirib have been discussing this also:
http://forum.snitz.com/forum/topic.asp?TOPIC_ID=24361
Cheers,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them!
Edited by - Bunce on 14 March 2002 06:55:58 |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 14 March 2002 : 07:11:45
|
let us reiterate, we write his code not just for Access, and also to run an many different serveres.
we already have functions to deal with the dates, they work perfectly ok and do not cause any problems for any of the databases we support
|
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 14 March 2002 : 08:41:22
|
quote:
let us reiterate, we write his code not just for Access, and also to run an many different serveres.
 Exaclty what I was getting at. Hence my comment:
quote:
Isn't ISO ("YYYY/MM/DD TIME") compatible with pretty much all formats?
Not that there's anything wrong with text storage, but it does seem pretty rare to have dates stored as text. Most apps, if worried about compatibility, will store dates in numeric ISO format.
This avoids compatibility issues, allows for sorting since the numeric format is 'heirarchial' (biggest > smallest), and portability to other platforms.
AFAIK, most databases actually end up storing dates as numbers anyway. Perhaps because the storage requirement is less than for a string? Not sure about that though.
Anyway, I do realise we have existing formatting routines - just adding an opinion - Only one set of queries would still be required.
Cheers,
Andrew
Edited by - Bunce on 14 March 2002 08:54:53 |
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 14 March 2002 : 08:58:25
|
quote:
let us reiterate, we write his code not just for Access, and also to run an many different serveres.
Exaclty what I was getting at. Hence my comment:
quote:
Isn't ISO ("YYYY/MM/DD TIME") compatible with pretty much all formats?
Not that there's anything wrong with text storage, but it does seem pretty rare to have dates stored as text. Most apps, if worried about compatibility, will store dates in numeric ISO format.
This avoids compatibility issues, allows for sorting since the numeric format is 'heirarchial' (biggest > smallest), and portability to other platforms.
AFAIK, most databases actually end up storing dates as numbers anyway. Perhaps because the storage requirement is less than for a string? Not sure about that though.
Anyway, I do realise we have existing formatting routines - just adding an opinion - Only one set of queries would still be required.
Cheers,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them! |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 14 March 2002 : 09:02:43
|
Ok, I tried this, and you still need to add # to a query in access if you use datetime types, which brings us back to our original argument.
|
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 14 March 2002 : 09:23:26
|
(Oops, sorry about double post).
Sure - point taken HuwR.
I was referring to any advantages (if any in regards to Snitz) of storing the dates in *Numeric* (ISO) format, rather than text.
It may be "six of one, half dozen the other" - I admit I haven't looked at the coding of any of the date functions in Snitz yet.
Cheers,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them! |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 14 March 2002 : 09:29:00
|
quote:
I was referring to any advantages (if any in regards to Snitz) of storing the dates in *Numeric* (ISO) format, rather than text.
Since in order to do this we would require two queries every time a date was involved, I don't really see an advantage
|
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 14 March 2002 : 16:45:45
|
Stored as NUMERIC, not as a DATE/TIME => 1 set of queries. 
Leave it with me - I'll find some examples, check out the Snitz code and see if I can't explain more clearly what I'm on about.
Cheers,
Andrew
Edited by - Bunce on 14 March 2002 16:46:49 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 14 March 2002 : 17:08:44
|
Umm they are stored as "numeric" now, the field type may not be numeric, but thats basically what they are now isn't it ?
ASP Variables are variant types anyway so they don't really even care whether its numeric or text when you do the compare you can still do an
if M_DATE < 20010101 then
type clause can't you ? ie a numeric compare.
www.daoc-halo.com
Edited by - Gremlin on 14 March 2002 17:10:23 |
|
|
| |
Topic |
|
Topic Locked
