How to get a date to stay the right format

Hi all, just wondering how I get the date to stay in the same format, no default

dd / mm / yy - this is the format I want to use

using ASP Classic and MS Access 2003

example date: 1/10/17
-everytime i update this is changes from 1/10/17 to 10/1/17 and then back.
Graemewebber4technologiesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you can use the FormatDateTime function, but that'll still give a for character year, so maybe a custom function would be better:

function customFormatDate( d )
     customFormatDate = padZero( Day( d ) & "/" & padZero( Month( d ) & "/" & Right( padZero( Year( d ), 2 )
end function

Open in new window


and you would call it like:

Response.Write customFormateDate( now )
0
Graemewebber4technologiesAuthor Commented:
4 character year is fine for me, FormatDateTime ill try out and get back to you

cheers
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, it should work the way you want to as long as your server date format settings are dd/mm/yyyy.

example:

Response.Write FormatDateTime( now, 2 )
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Graemewebber4technologiesAuthor Commented:
Just looking at FormatDateTime here: http://www.w3schools.com/asp/func_formatdatetime.asp

not seeing dd / mm / yyyy ??

I only see mm / dd / yyyy
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I believe the format is based off of your local servers date/time locale settings. Give it a shot and see what happens!
1
Graemewebber4technologiesAuthor Commented:
Still does the same

would I have to put it on the Response.Write ? or the Executed Query? or both?

I currently have it on the Executed Query
0
Graemewebber4technologiesAuthor Commented:
also tried putting it on the Response.Write, same thing too
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I would set it as its own variable, then use that in your query.

Did you try the custom function I gave you?
0
Graemewebber4technologiesAuthor Commented:
would it matter with the date...

<input type=text> put in the there and submitted, when you go back to update it, that is when it changes, and you can keep clicking update and it changes around
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post your updated code?
0
Graemewebber4technologiesAuthor Commented:
oConn.Execute("UPDATE events SET event_name='"&Request.Form("eventname")&"',event_start=#"&FormatDateTime(Request.Form("eventstart"),2)&"#,event_finish=#"&FormatDateTime(Request.Form("eventfinish"),2)&"#,event_location="&GPLocations("location_ID")&",event_code='"&Request.Form("codeID")&"',event_conf="&EventConf&",event_live="&EventLive&" WHERE event_ID="&Request.QueryString("event"))

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you do:

Response.Write "UPDATE events SET event_name='"&Request.Form("eventname")&"',event_start=#"&FormatDateTime(Request.Form("eventstart"),2)&"#,event_finish=#"&FormatDateTime(Request.Form("eventfinish"),2)&"#,event_location="&GPLocations("location_ID")&",event_code='"&Request.Form("codeID")&"',event_conf="&EventConf&",event_live="&EventLive&" WHERE event_ID="&Request.QueryString("event"))

then, whatever is outputted to the screen, run directly in your database and see if you get any results
0
Graemewebber4technologiesAuthor Commented:
have been running it in my database, however because it is an UPDATE query, it doesnt produce a result?

however when it updates the DB, it changes it back and forth from 1/10/2017 to 10/1/2017 and vv
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
even when using my custom function?

oConn.Execute("UPDATE events SET event_name='"&Request.Form("eventname")&"',event_start=#"&customFormatDate(Request.Form("eventstart"))&"#,event_finish=#"&customFormatDate(Request.Form("eventfinish"))&"#,event_location="&GPLocations("location_ID")&",event_code='"&Request.Form("codeID")&"',event_conf="&EventConf&",event_live="&EventLive&" WHERE event_ID="&Request.QueryString("event"))
0
Graemewebber4technologiesAuthor Commented:
going to try the custom function now
0
Graemewebber4technologiesAuthor Commented:
just having a look at some other code I have...

the difference between  ' " & 1/10/2017 & " ' and # " & 1/10/2017 & " # ??
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
It should read using Format:
oConn.Execute("UPDATE events SET event_name='"&Request.Form("eventname")&"',event_start=#"&Format(Request.Form("eventstart"),"yyyy\/mm\/dd")&"#,event_finish=#"&Format(Request.Form("eventfinish"),"yyyy\/mm\/dd")&"#,event_location="&GPLocations("location_ID")&",event_code='"&Request.Form("codeID")&"',event_conf="&EventConf&",event_live="&EventLive&" WHERE event_ID="&Request.QueryString("event"))

Open in new window

/gustav
0
Graemewebber4technologiesAuthor Commented:
Hi Gustav, this will insert the date as dd / mm / yyyy ?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Format() won't work, it's a visual basic function, not vbscript.

To answer your previous question, you should surround the date with pound signs
0
Graemewebber4technologiesAuthor Commented:
Ah ok, I know I have a couple of other statements with the single quotes, but they are INSERTS
0
Gustav BrockCIOCommented:
Well, what do you see?

I don't know what you read from the form. You may have to apply DateValue as well:

    event_finish=#"&Format(DateValue(Request.Form("eventfinish")),"yyyy\/mm\/dd")&"#,

/gustav
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
again, there is no FORMAT() function in classic asp, you need to use formatDateTime(), as I suggested earlier. or use the custom function I gave you.

Did you try that? what were your results? I can't help you if you don't respond to the questions/solutions I propose
0
Graemewebber4technologiesAuthor Commented:
Hi BigMonty, sorry I was trying to figure out where the missing brackets went, got that sorted.

Error now:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'padZero'

/admin2.asp, line 9 -> customFormatDate = padZero( Day( d ) )& "/" & padZero( Month( d ) )& "/" & Right( padZero( Year( d ), 2 ))

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
dd this function anywhere on your page:

    function padZero( n )
        if CInt( n ) < n then
            padZero = "0" & n
        else
            padZero = n
        end if
    end function
0
Graemewebber4technologiesAuthor Commented:
I have just tried replacing # with quotes and it inserts and doesn't change on recall and update... its working... ? is this supposed to happen? with quote and not # ?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
the best way to tell if it's working is to see if the data is there in the database and in the format you want :)
0
Graemewebber4technologiesAuthor Commented:
yeah its all working great, no issues at all

why would there be a difference?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
not sure why, both formats should have worked
0
Graemewebber4technologiesAuthor Commented:
Interesting... how do we close off this question now?

I mean you have been a help is debugging it and trying different stuff...
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i can't tell you how to close a question, I think EE may object to that :)

if any of my answers helped you and you think they may be useful to others, then select them. if not, delete the question and it'll auto-delete in 4 days.

FYI - I'm not here for the points, so as long as you got the solution, I don't care how :)
0
Graemewebber4technologiesAuthor Commented:
tried something not suggested and worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.