dates in MS Access not behaving.

[redmeat]

I'm using MS Access databse to store dates and times, I have fields:

dates: ##/##/####
times: ##:##:##

When viewed in the database, the dates and times are nice and clean, and keep to their input masks, (above). However when I pull them from the database, using ASP, I get this:

If I pull the date field, it gives me the date in word format, and 0's for the time. I don’t want the time, all I specified was the date – and in mm/dd/yyyy format, not words! E.g.

Sun Nov 17 00:00:00 UTC 2002 12:26:45


If I pull the time field from the database I get this:

Sat Dec 30 13:05:32 UTC 1899

Presumably because the time has been set, but the date hasn't.

Why can't I get just the date value that the database shows when viewed directly by opening up the database.

I thought input masks would do the trick, but they seem to do not much at all.

Also, how do I overcome the problem of date or times if their less than 10, taking this format 9, instead of 09?

Many thanks to anyone who can offer their help.

Jack.

/edited because:

If I store them as strings in the database then the format is fine, but not really any use. And that's daft stroing dates as strings when there is a perfectly good format there to deal with dates and time.

[whammy]

Actually what you need to do is change your datetime field setting in Access itself to always show MM/DD/YYYY - I don't have it installed at home, but I could tell you how when I get back to work tomorrow.

If you mess around with it a bit you should be able to find the option though, I did. ;)

[redmeat]

Oh , yeah I've done that, it's got strict masks and formatting, but when I pull using ASP the date it gives me loads of stuff.

[whammy]

Oh, if you're pulling it using ASP try these:

FormatDateTime(sDate,1)
FormatDateTime(sDate,2)
FormatDateTime(sDate,3)
FormatDateTime(sDate,4)

I think 2 is the one you want, don't remember offhand. I think that one is NN/NN/NNNN

[whammy]

Oh yeah, if you REALLY want to format stuff, like "09" instead of "9", you might want to get the datepart()s of the date and reformat it yourself if the above technique doesn't work.

Like:

DisplayDate = Right("00" & DatePart(Day,rs("mydate")) ,2) & _
"/" & Right("00" & DatePart(Month,rs("mydate")),2) & _
"/" & DatePart(Year,rs("mydate"))

Response.Write(DisplayDate)

:D

[redmeat]

I'm a dingbat (edited by whammy), because I didn't mention I write ASP using JScript, although I think I'll be changing after this. Here's the solution I found in the end:

http://www.4guysfromrolla.com/webtech/code/FormatDateTime.shtml

Watch the language, eh? There are kids here! - Whammy

[whammy]

Glad you found the solution. Yeah, VBScript has some nifty Date formatting functions built-in as opposed to JScript...

BTW, you can use VBScript in a JScript page (and vice versa):

<script language="VBScript" runat="server">
Function GetDateFormat(byVal, sDate)
GetDateFormat = Right("00" & DatePart(Day,sDate),2) & _
"/" & Right("00" & DatePart(Month,sDate),2) & _
"/" & DatePart(Year,sDate)
End Function
</script>

You should be able to call that from JScript with no problem (just like calling a JScript function) - haven't checked for syntax errors though.

[Vladdy]

Switching to VBScript, huh?
Unleash the power of regular expressions and never experience the need for any formating functions again :D :D

[whammy]

Yeah... i.e.:

http://www.solidscripts.com/downloads/regex.txt

:)