Leading Zeros on a Timestamp Date 1

[d222222]

I am retrieving a Timestamp date that has been saved in the database but the format doesn't have leading 0's and I need them to sort the date field correctly. However it also has the /'s and the minutes in the format. How can I do this?

 

[BigRed1212]

Could you provide an example of the data and perhaps the database layout along with any code you may have tried?

People are more likely to respond if they don't have to guess and they think you have done some of the work.

 

[d222222]

I have a timestamp saved to the database and when I retrieve the data it looks like this:

5/16/2008 4:08:21 PM
5/2/2008 10:22:01 AM

When I retrieve it, I have it sorted by this date and it is in the correct order. I want the most recent item listed at the top like it is here. However I also have code in the table to sort each column. If they have sorted another column and then try to sort this column again, it doesn't recognize it as a date field and puts it in alphanumeric order:

5/2/2008 10:22:01 AM
5/16/2008 4:08:21 PM

The sort would work if it had leading zeros like this:

05/02/2008 10:22:01 AM
05/16/2008 04:08:21 PM

I have looked on the internet and the only code I can find is a way to add the leading zeros before it goes into the database by breaking down the current date into parts (MM, DD, YYYY). However I already have data in the database saved in the other format and don't want to have to change all of the exising data. I would rather retrieve it and put it in the format I need for displaying and sorting.

I hope that explains it better. Thanks.

 

[johnwm]

You could use the split process on the database output before sending it to the client using the Datepart function:

http://www.w3schools.com/vbscript/func_datepart.asp

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jfdabiri]

The sort would work if it had leading zeros like this:
05/02/2008 10:22:01 AM
05/16/2008 04:08:21 PM
"""""""
actually your data is not in "sortable" order. if you really want to sort, it should look like this:

YYYY/MM/DD HH24:mm:ss
you can split the whole string separated by spaces.
part(0) will be the date, part(1) will be time and part(2) will be am/pm.
[code]
datestring = split(the_whole_string, " ")
dateparts = datestring(0)
dateitems = split(dateparts, "/")
mm = dateitems(0)
dd = dateitems(1)
yyyy = dateitems(2)
new_date = yyyy & "/" & mm & "/" & dd

 

[jfdabiri]

to make mm and dd always 2 digits, use:
mm = right("00" & mm, 2)
dd = right("00" & dd, 2)

 

[d222222]

This is what I tried and it didn't return any records:

<%		
strDate = objRsFolder("Timestamp")
datestring = split(strDate, " ")
dateparts = datestring(0)
dateitems = split(dateparts, "/")
mm = dateitems(0)
dd = dateitems(1)
yyyy = dateitems(2)
sDate =  yyyy & "/" & mm & "/" & dd
%>

<td>
<%= sDate %>
</td>

Also, you mention the code that will always make 2 digits. Is that for saving to the database or retrieving?

 

[jfdabiri]

what's your database? access? oracle? or ?
i tested the code above and it worked. how are you testing it? try writing sdate for test purposes. the two-digit thing works for displaying and sorting. if you write to the database, you have to use # for access tables and make_date(sdate) for oracle.

 

[jfdabiri]

sorry about being vague:
in access the date should be wrapped in #
e.g. #05/20/2008#
in oracle, it's not make_date it's TO_DATE(your_date_string)
e.g. to write to the database use:
TO_DATE('05/20/2008 14:53:10', 'MM/DD/YYYY HH24:MI:SS')
and to read from the database use:
TO_CHAR(date_string, 'MM/DD/YYYY HH24:MI:SS')

 

[d222222]

I'm using SQL.

 

[d222222]

I think the best way to do this is to have code that will count how many characters there are from the left to the first "/". If there is only one character, add a "0". Then repeat to the next "/".

 

[d222222]

I'm sorry. Your code did work when I put it elsewhere in my code. It just didn't work where I have a loop so I will have to adjust the code. Thank you for your help.

 

[jfdabiri]

when you use split(date_string, "/")
it gives you different parts of the date: mm, dd, yyyy
no need to "count" until you reach "/"
and to make mm and dd 2 digits, the best way is to concatenate "0" and the one digit, and take the 2 right most bites:
mm = 5
dd = 3
new_mm = right("0" & mm, 2) ' new_mm is now "05"
new_dd = right("0" & dd, 2) ' new_dd is now "03