Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to get a date to stay the right format

Posted on 2016-09-12
31
Medium Priority
?
47 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 51

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 51

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

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