date formats

Hello

I am trying to standardize how dates are stored on my site.

In one place they appear as:
3/13/2004
3/14/2004
3/21/2004

etc...

and another they appear as:
1079551798
1079334828
1079368763

how do I convert the 1st few dates into the 2nd format.

Thanks




CookieMonster9999Asked:
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.

alorentzCommented:
Please maintain you old questions before continuing:

1 03/22/2004 500 date formats  Open Active Server Pages (ASP)
2 03/21/2004 500 music files with different bit rates  Open Web Development
3 03/18/2004 250 uppercase vs. lowercase  Open Active Server Pages (ASP)
4 03/15/2004 500 batch mp3 preview making  Open Web Development
8 02/13/2004 500 Locked out of files  Open Windows XP

Please let me know if you need assistance.
0
RedLavaCommented:
Hi CookieMonster9999,

I guess that the 2nd format is a serial date format.

The following code converts standard date to serial date:

  <% DateSerial = Date - CDate("January 1, 1900") + 1 %>

RedLava
0
CookieMonster9999Author Commented:
hm... that doesn't seem to be it.

php is inserting time() into the database...

does time include both date and time in php?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

RedLavaCommented:
PHP??
If the database is just storing time then how are you displaying the dates?
now() will store both date and time where as date stores just the date.

The code to use to solve your problem doesn't interface with the database it acts on the date variable.

Place your date from the first output in the 'date' area of the code.

e.g.

  <% NewOutput = OldOutput - CDate("January 1, 1900") + 1 %>

RedLava
0
CookieMonster9999Author Commented:
I tried the code that you posted but it results in entries like this:

38033
38040
38040
38054
38054


which don't seem to match php entries which look like this:

1079551798
1079334828
1079368763



the php entries are coming from the phpbb script which reads:

$sql = "INSERT INTO " . USERS_TABLE . "      (user_id, username, user_regdate, user_password)
VALUES ($user_id, '" . str_replace("\'", "''", $username) . "', " . time() . ", '" . str_replace("\'", "''", $new_password) . "')
0
CookieMonster9999Author Commented:
I have since discovered that phpbb is using the UNIX timestamp which measures the number of seconds since 1st January 1970.

0
RedLavaCommented:
Is this a PHP or ASP question?
0
CookieMonster9999Author Commented:
I'm trying to convert a date and time like 3/22/2004 11:24:00 PM into the phptimestamp format for time which is counting the number of seconds from Jan 1st 1970 until that moment.

I am coding this in ASP
0
CookieMonster9999Author Commented:
0
CookieMonster9999Author Commented:

For anyboyd else that is interested- to get the UNIX timestamp of a current date and time in VBscript:


DateDiff("s", "01/01/1970 00:00:00", now())
0
hb21l6Commented:
unix time fields are stored as long int strings e.g. 1065103487. This is the same as 02/10/2003 14:04:47. so how is it worked out? very simply, the unix time divided by seconds in a day then add the difference from Microsofts startdate.. unix time starts from the 1 jan 1970 and is counted in seconds. there is also something similar for Microsoft. theres starts at 1 jan 1900. the difference between then in seconds is 25569. seconds in a day is 86400. so the formula looks like this MSseconds = ((unixseconds/86400)+25569).. if that makes sense?


example::


<html>


<b>UNIX TIME CONVERTER<br>
</b>
<small>
This field is a
standard UNIX time field; that is, the time is stored as
the number of seconds since January 1, 1970.
</small>

<hr>

<form name="frm1" method="post" action="#">

<input type="text" name="unixvalue">

<input type="submit" value="convert">
<input type="hidden" name="pageaction" value="nextpage">


e.g. 1065103487 or 1065109172

</form>


<hr>


<%

if request("pageaction") = "nextpage" then

unixvalue = request("unixvalue")

response.write "unix date was " & unixvalue & " and now is... <br><br>"

                ' here is the function to handle the conversion
propertime = ((unixvalue/86400)+25569)



response.write "<br>1: " &  FormatDateTime(propertime, 1)
response.write "<br>2: " &  FormatDateTime(propertime, 2)
response.write "<br>3: " &  FormatDateTime(propertime, 3)
response.write "<br>4: " &  FormatDateTime(propertime, 4)
response.write "<br>5: " &  FormatDateTime(propertime, 0)

response.write  strFormTime


end if
%>

I posted this a while back on bantrax.


any probs let me know!!
hb

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
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.