yyyy-mm-dd

[Cullen411]

Just a minor issue,
Before I insert a date into an Access database I am trying to convert to the format YYYY-MM-DD.

Is 1964-7-16 treated exactly the same as 1964-07-16? or should I add 0 to the 7 and then insert?

 

[DotNetGnat]

add a zero...

-DNG

 

[Tarwn]

Please don't do this.

Insert the data into a Date/Time field in the database. If you have other applications that need a specific format later on, make them do the conversion. If you are using a Date/Time field right now, then the format your putting in with doesn't matter, it won't be saved. If it is a text field then you will not be able to do anything with that data in the future. Comparisons will do text comprisons (or will require date conversions on the fly in your SQL statement).

The database should store data. Leave it up to your applications to determne how that data will be presented.

 

[onpnt]

The database should store data. Leave it up to your applications to determne how that data will be presented.

to that

 

[Cullen411]

The format you insert does matter.

Here's a scenario - you insert a date into an access database which is hosted on an american server. Then you move the Access database to a British server and run the code and insert a date.
Open the database and both dates are in a different format.
Use YYYY-MM-DD and they are not.

 

[chrissie1]

Here's a scenario - you insert a date into an access database which is hosted on an american server. Then you move the Access database to a British server and run the code and insert a date.
Open the database and both dates are in a different format.

this can never be true since access doesn't really save the dat as such it converts it to an integer or long. That's why access datefield has a beginning (minimum, 0 if you like) and an end (maximum,0 + ...). so access doesn't care how you format them. and it will choose the computers datformat to show it.
Perhaps you are using a string to store your date wich is very wrong.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[Cullen411]

Chrissie
that's exactly what happened why I ran the code

SQL="INSERT INTO tblDate (dDate) VALUES (#" & Now() & "#)"
on both servers.

I've now got these two records in my database - bear in mind the day difference between servers

08/12/2005 22:25:28
12/09/2005 06:26:59

 

[Tarwn]

Perhaps a better way to add the data would be to just specify an LCID for this single page, that would force the page to use one set of settings rather than adopting the servers locale. I agree in this one specific occasion that formatting the date might make sense, but there are simpler solutions.

 

[chrissie1]

Doesn't asp have parameters??

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[Bullschmidt]

And to make a variable be in the format of mm/dd/yyyy (and the final line of code can be modifed for other date formats), perhaps try something like the following which you might even make into a function that you could put on a page that gets included in your main pages:

varFld = CDate(MyVariable)

intMonth = Month(varFld)
intDay = Day(varFld)
intYr = Year(varFld)

If intMonth < 10 Then
strMonth = "0" & CStr(intMonth)
Else
strMonth = CStr(intMonth)
End If

If intDay < 10 Then
strDay = "0" & CStr(intDay)
Else
strDay = CStr(intDay)
End If

strYr = Right(CStr(intYr), 4) ' And change the 4 to 2 for 2 year dates.

varFld = CStr(strMonth & "/" & strDay & "/" & strYr)


Best regards,
J. Paul Schmidt, Freelance Web and Database Developer

http://www.Bullschmidt.com

Access Database Sample, Web Database Sample, ASP Design Tips

 

[tsdragon]

Instead of using an if statement to pad a number I prefer to do it this way:

strMonth = Right("00" & CStr(intMonth),2)

Just a different way of doing the same thing, but more flexible in that it can easily be applied to longer numbers too.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[Bullschmidt]

Good idea - thanks!

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer

http://www.Bullschmidt.com

Access Database Sample, Web Database Sample, ASP Design Tips