Solved

How to get a date to stay the right format

Posted on 2016-09-12
31
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 13
  • 2
31 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 41794125
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
ID: 41794212
4 character year is fine for me, FormatDateTime ill try out and get back to you

cheers
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41794219
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Graeme
ID: 41794221
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 33

Expert Comment

by:Big Monty
ID: 41794228
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
ID: 41794277
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
ID: 41794284
also tried putting it on the Response.Write, same thing too
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41794330
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
ID: 41794334
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 33

Expert Comment

by:Big Monty
ID: 41794340
can you post your updated code?
0
 

Author Comment

by:Graeme
ID: 41794346
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 33

Expert Comment

by:Big Monty
ID: 41794362
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
ID: 41794413
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 33

Expert Comment

by:Big Monty
ID: 41794446
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
ID: 41794448
going to try the custom function now
0
 

Accepted Solution

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

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

Expert Comment

by:Gustav Brock
ID: 41794626
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
ID: 41794630
Hi Gustav, this will insert the date as dd / mm / yyyy ?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41794649
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
ID: 41794654
Ah ok, I know I have a couple of other statements with the single quotes, but they are INSERTS
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41794657
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 33

Expert Comment

by:Big Monty
ID: 41794699
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
ID: 41794742
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 33

Expert Comment

by:Big Monty
ID: 41794754
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
ID: 41794780
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 33

Expert Comment

by:Big Monty
ID: 41794791
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
ID: 41794811
yeah its all working great, no issues at all

why would there be a difference?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41794837
not sure why, both formats should have worked
0
 

Author Comment

by:Graeme
ID: 41795395
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 33

Expert Comment

by:Big Monty
ID: 41795973
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
ID: 41808397
tried something not suggested and worked
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

730 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