Solved

How to get a date to stay the right format

Posted on 2016-09-12
31
33 Views
Last Modified: 2016-09-21
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.
0
Comment
Question by:Graeme
  • 16
  • 13
  • 2
31 Comments
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
4 character year is fine for me, FormatDateTime ill try out and get back to you

cheers
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
I believe the format is based off of your local servers date/time locale settings. Give it a shot and see what happens!
1
 

Author Comment

by:Graeme
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
also tried putting it on the Response.Write, same thing too
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
I would set it as its own variable, then use that in your query.

Did you try the custom function I gave you?
0
 

Author Comment

by:Graeme
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
can you post your updated code?
0
 

Author Comment

by:Graeme
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
going to try the custom function now
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Accepted Solution

by:
Graeme earned 0 total points
Comment Utility
just having a look at some other code I have...

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

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
Hi Gustav, this will insert the date as dd / mm / yyyy ?
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
Ah ok, I know I have a couple of other statements with the single quotes, but they are INSERTS
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Comment

by:Graeme
Comment Utility
yeah its all working great, no issues at all

why would there be a difference?
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
not sure why, both formats should have worked
0
 

Author Comment

by:Graeme
Comment Utility
Interesting... how do we close off this question now?

I mean you have been a help is debugging it and trying different stuff...
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
 

Author Closing Comment

by:Graeme
Comment Utility
tried something not suggested and worked
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now