Take a timestamp and convert to date 1

[optjco]

I have this function that is converting a timestamp into a date however i can only get it to show the date as this

Jan 11 2005 18:11:00

But I Need to format date into this

Tue Jan 11 2005 6:11pm

Here is the function

function timestamp_to_date(ts)
 if ts<>"" and isnumeric(ts) then
  timestamp_to_date = (dateadd("s",ts,"01/01/1970 00:00:00)
 end if
end function

can anyone tell me if this is possible please ?



Regards

Olly

 

[neilharris]

Try this

function timestamp_to_date(ts)
   timestamp_to_date = format(ts,"ddd mmm yyyy H:Nnam/pm
end function

 

[optjco]

Neil,
Thanks a lot I will try that tomorrow when I get back to work

Regards

Olly

 

[DreXor]

format doesn't always work in ASP unless you've made the workarounds necessary for it.

you may also want to look into formatdatetime() if format() doesn't work for you.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never inside the 'loop' " - DreX 2005

 

[optjco]

Guys,
I tried both

function timestamp_to_date(ts)
   timestamp_to_date = format(ts,"ddd mmm yyyy H:Nnam/pm
end function

and

function timestamp_to_date(ts)
   timestamp_to_date = formatdatetime(ts,"ddd mmm yyyy H:Nnam/pm
end function

But got an error about datatype mismatch on string

Here is a little more background on what me and a colleague are trying to achieve.He has an mySQL database that stores a ten digit timestamp and he was hoping to display as shown in the original thread however he did not post me all of his function which is shown below in it's entiretity

function timestamp_to_date(ts)
 if ts<>"" and isnumeric(ts) then
   ts_temp = formatdatetime((dateadd("s",ts,"01/01/1970 00:00:00")),1) & " " & formatdatetime((dateadd("s",ts,"01/01/1970 00:00:00")),3)
  timestamp_to_date=ts_temp
 end if
end function

The first part
formatdatetime((dateadd("s",ts,"01/01/1970 00:00:00")),1)

extracts the date and the second part
formatdatetime((dateadd("s",ts,"01/01/1970 00:00:00")),3)
extracts the time

I thought a liitle more info may help to put us in the right direction, a sample timestamp value is as follows

1105467069

using the function we have now This is displaying as
11 January 2005 18:11:09

But would like it to display as

Tue Jan 11 2005 6:11PM

We do not seem to be able to get either the day i.e. Tue,Wed etc or the PM to work



Regards

Olly

 

[Tarwn]

Yikes.

Actually his two functions are both extracting DateTime's it's just that he is then concatenating together two differant formats. Though I guess I have been guilty of doing a lot worse in the name of one line executable code

None of the formats are going to be able to generate the format you would like to see. Your going to have to build your own format function. This actually won't be very hard as there are a lot of built-in date/tiome functions in VBScript.

In fact, I would suggest not mixing your date extraction logic with your conversion logic simply because it would probably be nice to be able to later re-use the formatting for other dates (like Now()). My suggestion would be to do something laong the lines of:

Function timestamp_to_date(ts)
	If ts <> "" And IsNumeric(ts) Then
		timestamp_to_date = DateAdd("s",ts,#01/01/1970 00:00:00#)
	Else
		timestamp_to_date = Nothing
	End If
End Function


Function FormatDateSpecial(aDate)
	If IsDate(aDate) Then
		'3 letter day name
		FormatDateSpecial = Left(WeekDayName(WeekDay(aDate)),3)
		'3 letter month
		FormatDateSpecial = FormatDateSpecial & " " & Left(MonthName(Month(aDate)),3)
		'day of month
		FormatDateSpecial = FormatDateSpecial & " " & Day(aDate)
		'year
		FormatDateSpecial = FormatDateSpecial & " " & Year(aDate)
		'time
		FormatDateSpecial = FormatDateSpecial & " " & (Hour(aDate) mod 12) & ":" & Minute(aDate) & " " 

		'AM/PM
		If Hour(aDate) < 12 Then
			FormatDateSpecial = FormatDateSpecial & "AM"
		Else
			FormatDateSpecial = FormatDateSpecial & "PM"
		End If
	Else
		FormatDateSpecial = Nothing
	End If
End Function

Of course if you want a shorter version of the formatting function then you could do something like:

Function FormatDateSpecial(aDate)
	If IsDate(aDate) Then FormatDateSpecial = Left(WeekDayName(WeekDay(aDate)),3) & " " & Left(MonthName(Month(aDate)),3) & " " & Day(aDate) & " " & Year(aDate) & " " & (Hour(aDate) mod 12) & ":" & Minute(aDate) & " " & Mid("AP",Fix(Hour(aDate)/12) + 1,1) & "M"
End Function

Sorry, probably an easy one liner, but I liked the AM/PM solution I came up with

-T

 

[optjco]

Tarwn,
tried the shorter version in a test page however I cannot get the page to display, here is the code

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Forums.asp" -->
<%
Dim RSdate
Dim RSdate_numRows

Set RSdate = Server.CreateObject("ADODB.Recordset")
RSdate.ActiveConnection = MM_Forums_STRING
RSdate.Source = "SELECT DT_DateTime  FROM TestDate"
RSdate.CursorType = 0
RSdate.CursorLocation = 2
RSdate.LockType = 1
RSdate.Open()

RSdate_numRows = 0
%>
<html>
<head>
<%
Function FormatDateSpecial(aDate)
    If IsDate(aDate) Then FormatDateSpecial = Left(WeekDayName(WeekDay(aDate)),3) & " " & Left(MonthName(Month(aDate)),3) & " " & Day(aDate) & " " & Year(aDate) & " " & (Hour(aDate) mod 12) & ":" & Minute(aDate) & " " & Mid("AP",Fix(Hour(aDate)/12) + 1,1) & "M"
End Function

%>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<input name="textfield" type="text" value="<%=(FormatDateSpecial(RSdate.Fields.Item("DT_DateTime")).Value)%>">
</body>
</html>
<%
RSdate.Close()
Set RSdate = Nothing
%>

any ideas ??

Regards

Olly

 

[Tarwn]

I broke your one function into two functions so they would be more useable. Thefirst function simply takes a number of seconds and makes a new date object based on that number. The second function (FormatDateSpecial) will take any date object and convert it to your format. This way you have greater flexibility should you need to compare a date against your number of seconds (simply use the first function and you have a date object and can DateDiff) as well as the capability of outputting any date object (Now() for instance) in the format you set up (usingthe new second function).

I would sugest for maintainability that you usethe longer version of the second function, the shorter version will be harder to maintain and was more for fun.

Sample:

'pretend we have a variable someSecs with a number of seconds in it
Dim myDate
'convert the seconds to a date object
myDate = timestamp_to_date(someSecs)
'output our date object with the special formatting
Response.Write FormatDateSpecial(myDate)

'or output the current date/time with the special fomratting
Response.Write FormatDateSpecial(Now())

'or figure out the difference in days between our seconds and Now
Response.Write "The first date occurred " & DateDiff("d",myDate,Now()) & " days ago."

Function timestamp_to_date(ts)
    If ts <> "" And IsNumeric(ts) Then
        timestamp_to_date = DateAdd("s",ts,#01/01/1970 00:00:00#)
    Else
        timestamp_to_date = Nothing
    End If
End Function


Function FormatDateSpecial(aDate)
    If IsDate(aDate) Then
        '3 letter day name
        FormatDateSpecial = Left(WeekDayName(WeekDay(aDate)),3)
        '3 letter month
        FormatDateSpecial = FormatDateSpecial & " " & Left(MonthName(Month(aDate)),3)
        'day of month
        FormatDateSpecial = FormatDateSpecial & " " & Day(aDate)
        'year
        FormatDateSpecial = FormatDateSpecial & " " & Year(aDate)
        'time
        FormatDateSpecial = FormatDateSpecial & " " & (Hour(aDate) mod 12) & ":" & Minute(aDate) & " "

        'AM/PM
        If Hour(aDate) < 12 Then
            FormatDateSpecial = FormatDateSpecial & "AM"
        Else
            FormatDateSpecial = FormatDateSpecial & "PM"
        End If
    Else
        FormatDateSpecial = Nothing
    End If
End Function

Basically the idea is to seperate the logic and presentation. Now you hae a great deal more flexibility, it just requires two function calls instead of one.

-T

 

[optjco]

Tarwn,
Thank you for the in depth reply as usual I did not grasp fully what you had done for me. I have tested the code in the ways you suggested and it works great.

Thanks for the help & a star for you



Regards

Olly