| Author |
 Topic  |
|
Sirius
Starting Member
45 Posts |
 Posted - 12 March 2002 : 17:09:43
|
I'm using ASP and Access 2000
In my SQL string I'll be storing a date in a date field in my DB such as "3/12/2002 4:00:27 PM". This stores it, but then when I look at the DB directly in Access or retrieve the date I've just stored it comes out as "12/3/2002 4:00:27 PM". The month and date have been interchanged. Obviously this is screwing up my DateDiff checks. There is probably a simple solution to this. Will someone help a noob out and point me in the right direction? Thanks in advance!
Edited by - Sirius on 12 March 2002 17:11:01 |
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 12 March 2002 : 18:26:04
|
Store your dates in the format "yyyy/mm/dd time" so that there can be no discrepencies.
Then use the inbuilt date functions of ASP to format the date as you wish for output.
Cheers,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them! |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 12 March 2002 : 19:05:18
|
I think the internal Access format is connected to your regional settings. At least I can store date values in Access and see them in the proper format.
I would recommend that you use the VbScript date funtions to get the date out in a proper format.
I can show you some sample code to do it if you need it.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 13 March 2002 : 02:45:16
|
It shouldn't matter how the storage of a date 'looks' in a database.
IMO, we should try to ALWAYS use ISO format ("YYYY/MM/DD"), and build an output function in the scripting language of your choice that you can reuse when required.
It saves future troubles in terms of porting to other databases, function use, regional differences etc.
http://www.adopenstatic.com/faq/DateswithAccess.asp
http://www.4guysfromrolla.com/webtech/011701-1.shtml
Cheers,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them!
Edited by - Bunce on 13 March 2002 03:19:38 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2002 : 07:56:30
|
Hi,
I agree with you that it's always better to have a standard way of dealing with dates, specially if you need to make portable code. But you need to use extra code for it to work, just as the very interesting examples in the links you provided show.
I do it differently from the examples in the code. I just get each part of the date to write to the DB and use it in the DateSerial VbScript function and store the returned value in Access. It's just simpler than the examples provided.
DateSerial takes (year,month,day) as parameters, so there is no way to make mistakes. If you know the date components (year, month,date) you can write them directly in DateSerial. If you don't it's pretty easy to separate them using InStr.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 13 March 2002 : 17:50:03
|
Yep, good points.
Guess it's a habitual thing I've got used to :=)
It's just amazing how many questions arise in newsgroups as to why date formatting / conversions don't work and most can usually be solved by storing and manipulating them this way.
Cheers,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2002 : 18:05:21
|
quote:
Guess it's a habitual thing I've got used to :=)
That happens frequently when you develop. You spend your time doing it once and then you just reuse the solution. This makes sense.
Sometimes it takes an outside view to "alert" you to alternative ways of doing things. That's why I value other people's views on things. Quite often they prompt you to review your own view of the matter and get a better solution (I'm not saying this is the case here, just describing what often happens with me...)
quote:
It's just amazing how many questions arise in newsgroups as to why date formatting / conversions don't work and most can usually be solved by storing and manipulating them this way.
You know most people don't even realize there are alternative ways to represent a date...
Anyway thanks for your links. They were interesting indeed.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Bunce
New Member
Australia
84 Posts |
Posted - 14 March 2002 : 06:25:15
|
Very true.
In this case though I've used a number of different 'date methologies' and having encountered many of the problems/issues that I read around the community, I've found the ISO method to be the solution (or prevention) in almost all cases.
If you get the chance Ru, check out the rest of Kens http://www.adopenstatic.com site. Quite interesting and informative. I often list his links in newsgoups etc. as they come up so often. (Plus I can't miss an opportunity to plug a fellow Aussie  - He hangs out on the 15Seconds ASP list and a number of the Wrox lists, which are quite popular and useful if you're interested).
Anyway, enough babble - Ciao for now,
Andrew
======================================================
There have been many, many forum posts made throughout the world...
This was one of them! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 March 2002 : 08:07:30
|
quote:
If you get the chance Ru, check out the rest of Kens http://www.adopenstatic.com site. Quite interesting and informative. I often list his links in newsgoups etc. as they come up so often. (Plus I can't miss an opportunity to plug a fellow Aussie - He hangs out on the 15Seconds ASP list and a number of the Wrox lists, which are quite popular and useful if you're interested).
Andrew
I had bookmarked adopenstatic. It's interesting enough, indeed. Also done that with 15seconds a while back.
Odd thing that my main ASP book is Wrox's Professional Active Server Pages 3.0 and I almost never go there. I guess you need to be in search of a solution that you don't have to really have the urge to look for it.
Thanks for the new tips.
Ciao to you too.
Rui
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
doog
Starting Member
3 Posts |
Posted - 18 March 2002 : 07:06:49
|
uhm
why don't you use ADODB.Command 's and let ADO do the work of date conversion?
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 18 March 2002 : 07:32:06
|
quote:
uhm
why don't you use ADODB.Command 's and let ADO do the work of date conversion?
ADO does no such thing. You don't take proper care you may get your month/day switched or maybe even generate error in date fields.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
doog
Starting Member
3 Posts |
Posted - 20 March 2002 : 08:46:49
|
quote:
ADO does no such thing. You don't take proper care you may get your month/day switched or maybe even generate error in date fields.
Sorry, that is not right
imagine the following example in vb
dim datevar as date
dim cmd as adodb.command
datevar=now
set cmd = new adodb.command
cmd.commandtype = adcmdtext
cmd.commandtext = "update tablename set datecolumn=?"
cmd.parameters.append cmd.createparam("datevalue1", adparaminput, addate, -1, datevar)
set cmd.activeconnection = hCon
cmd.execute
(where hCon is the database connection)
the db provider will handle the datevalue correctly with each kind of db, you do not need to care for it
the second advantage is, when you pass string values this way, the db provider will also take care of ampersants or comment signs
(do not kill me for spelling mistakes in the code, i wrote it by heart)
g
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 March 2002 : 08:55:23
|
You are forgetting one thing: the date format varies alot according to your locale. It maybe that the client and server settings are different and dates represented differently (month/day/year, day/month/year). If this happens you won't have the date properly written to the DB, in a format acceptable to the client. And this has been the subject of this discussion all along.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 20 March 2002 08:57:00 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 20 March 2002 : 08:56:29
|
Thats a similar solution you've proposed there to whats being discussed. the
cmd.createparm when used with the Addate parameter ... Doesnt actually store a date in the field. It stores the number of days difference between the date you supply and 12/30/1899. So in effect thats also negating the problem of date formats becuase your just using a single number representing the difference in days of 2 dates.
www.daoc-halo.com |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 20 March 2002 : 08:57:42
|
quote:
You are forgetting one thing: the date format varies alot according to your locale. It maybe that the client and server settings are different and dates represented differently (month/day/year, day/month/year). If this happens you won't have the date properly written to the DB, in a format acceptable to the client.
Which is exactly why people have suggested sticking with ISO format for the date, their is absolutely zero ambiguity then.
www.daoc-halo.com |
|
|
|
doog
Starting Member
3 Posts |
Posted - 20 March 2002 : 09:06:31
|
but that problem is not too hard to resolve
you send the client locale of the user within the http-post and do a setlocale in before converting the user input into the date variable
so every user can stick with the date format of his own
|
|
|
|
Topic |
|
Topic Locked
