Format Date 1

charbrad · Sep 10, 2007

I am trying to format a date in an ASP page in which the date already appears (prior to formatting). I would like the date to appear as 9/07/2007 instead of 2007-09-07 11:13:08.000. See code below

Code:

FormatDateTime(------, vbshortdate)

I am not sure what to put in place of the dashes. If I use Date or Now it gives me the current date in the proper format, but I want to utilize the date already populated in the SQL query.

Any suggestions?

Chopstik · Sep 10, 2007

Try using the variable returned from your SQL dataset.

Code:

FormatDateTime(rs.fields("myDateVariable", vbShortDate)

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Sep 10, 2007

I am not sure what you mean by variable, but what is being returned in the column is 2007-09-07 11:13:08.000. I will not be able to use this since all of the dates are different or does it matter?

Chopstik · Sep 10, 2007

What code produces 2007-09-07 11:13:08.000? That is your variable.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Sep 11, 2007

Thanks for your patience and your thorough explanations...I kinda inherited this without the necessary training. There is no calculation for this field. This is column retrieved from a select statement name stgdte. I am posting my code below....I feel I have many corrections and a long way to go.

Code:

<%
body = "<HTML><style type=""text/css""><!-- TD{font-size : 10px;font-family : verdana, helvetica, arial, sans-serif; color : #333333; margin-left : 2em;} 

--></style><BODY BGCOLOR=#ffffff><TABLE BORDER=1 BORDERCOLOR=#AAAAAA CELLPADDING=0 CELLSPACING=0>"
body = body + "<TR><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99"" COLSPAN=11><B>Order Short Report - Allocated Orders</TD></TR>"
body = body + "<TR><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Order #</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Customer Location</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Item Number</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Ship Date</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Status</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Customer PO#</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Stage Date</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Qty Ordered</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Picked Qty</TD><TD ALIGN=""CENTER"" 
BGCOLOR=""#99CC99""><B>Staged Qty</TD><TD ALIGN=""CENTER"" BGCOLOR=""#99CC99""><B>Short</TD></TR>"
SHADE = "#FFFFFF"
SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT ship_id, adrnam, prtnum, early_shpdte, shpsts, cponum, stgdte, host_ordqty, inpqty, stgqty, sum(host_ordqty-stgqty-inpqty) FROM ShipReport (NOLOCK) where (host_ordqty-stgqty-inpqty <> 0) GROUP BY ship_id, adrnam, prtnum, early_shpdte, shpsts, cponum, stgdte, host_ordqty, inpqty, stgqty ORDER BY early_shpdte, ship_id"
MyRecordSet.OPEN MySQL, dlxdb
MyArray = MyRecordSet.GETROWS()
MyRecordSet.CLOSE

I am still working on this portion to subtotal according to date....any suggestions you can give would be greatly appreciated.

Code:

'if early_shpdate <=CurrentDate then sum(cdbl(rs.fields("host_ordqty"))), sum(cdbl(rs.fields("inpqty"))), sum(cdbl(rs.fields("stgqty"))), sum(cdbl(rs.fields("host_ordqty-stgqty-inpqty"))) else
'if early_shpdate <=CurrentDate then cdbl(sum host_ordqty)), cdbl(sum(inpqty)), cdbl(sum(stgqty)), cdbl(sum(host_ordqty-stgqty-inpqty)) else 
'if early_shpdte = CurrentDate +1 then sum(cdbl(host_ordqty)), sum(cdbl(inpqty)), sum(cdbl(stgqty)), sum(cdbl(host_ordqty-stgqty-inpqty)) else 
'if early_shpdte > CurrentDate +1 then sum(cdbl(host_ordqty)), sum(cdbl(inpqty)), sum(cdbl(stgqty)), sum(cdbl(host_ordqty-stgqty-inpqty)) end if

Code:

FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
body = body + "<TR><TD BGCOLOR=""" & SHADE & """>" & MyArray(0,MyCursor) & "&nbsp;</TD><TD WIDTH=300 ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & 

MyArray(1,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>" & MyArray(2,MyCursor) & "&nbsp;</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & 

MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & MyArray(4,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" &

This is the portion of the code that I am looking to format the date.....

Code:

MyArray(5,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & FormatDateTime("stgdte", vbShortDate)  & "</TD><TD ALIGN=""CENTER""

Code:

BGCOLOR=""" & SHADE & """>" & MyArray(7,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & MyArray(8,MyCursor) & " </TD><TD 

ALIGN=""CENTER""BGCOLOR= """ & SHADE & """>" & MyArray(9,MyCursor) & " </TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & MyArray(10,MyCursor) & " 

</TD></TR>" & vbcrlf
IF SHADE = "#FFFFFF" THEN SHADE = "#DDDDDD" ELSE SHADE = "#FFFFFF"
body = body + "<TR><TD BGCOLOR=""" & SHADE & """ COLSPAN=7 ALIGN=""CENTER""><b>Total:</b>&nbsp;</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 

FormatNumber(inpqty,0) &"&nbsp;</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & FormatNumber(stgqty,0) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & 

SHADE & """><B>" & FormatNumber(stgqty,0) & "</TD><TD ALIGN=""CENTER""  BGCOLOR=""" & SHADE & """><B>" & FormatNumber(stgqty,0) & "</TD></TR>"
IF SHADE = "#FFFFFF" THEN SHADE = "#DDDDDD" ELSE SHADE = "#FFFFFF"

Chopstik · Sep 11, 2007

To fix your date issue, change this:

Code:

MyArray(5,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & FormatDateTime("stgdte", vbShortDate)  & "</TD><TD ALIGN=""CENTER""

to this (remove the quotes from around stgdte):

Code:

MyArray(5,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & FormatDateTime(stgdte, vbShortDate)  & "</TD><TD ALIGN=""CENTER""

As far as sub-totaling according to date, you'll need to refresh my memory as to what the problem is and what you're hoping to do. I think I remember seeing it in another thread but I don't remember which one (my memory is getting hazy as I get older).

Also, I've asked for your post to be modified so that you are not posting proprietary information.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Sep 11, 2007

Thanks for that...didn't realize my post until now. I will try this and let you know. I do have another post regarding the totalling issue...I will post my comments there and keep this post open for the date issue.

charbrad · Sep 11, 2007

When I try

Code:

MyArray(5,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & FormatDateTime(stgdte, vbShortDate)  & "</TD><TD ALIGN=""CENTER""

my date comes up as 12/30/1899...I am assuming that this is because there is no date being populated is there a way to leave this empty if no date is retrieved?

Chopstik · Sep 11, 2007

Yes, you can. One way that I normally handle it is to deal with it before I reach the point at which you're putting it in your HTML string. An example (which you can modify to fit your situation):

Code:

dim stgdte
if rs.fields("stgdte") <> "" or rs.fields("stgdte") then
  stgdte = FormatDateTime(rs.fields("stgdte"))
else
  stgdte = ""
end if 

[COLOR=green]'Here is where you will then use the new stgdte variable and properly formatted.[/color]
MyArray(5,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """>" & FormatDateTime(stgdte, vbShortDate)  & "</TD><TD ALIGN=""CENTER""

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Sep 11, 2007

I am going to try to make this my last question...at what point do I put

Code:

dim stgdte
if rs.fields("stgdte") <> "" or rs.fields("stgdte") then
  stgdte = FormatDateTime(rs.fields("stgdte"))
else
  stgdte = ""
end if

Also, who do I contact to edit my previous post?

Chopstik · Sep 11, 2007

I would put that code after you return your recordset from the database and before you attempt to write your HTML code.

Also, if you need to edit a post, you can click on the blue link in the middle of the screen under each post that reads "Inappropriate post? If so, Red Flag it!". Just for your own information, I red flagged it for you when I responded earlier with a description of the problem and how to best resolve it so that your post would not lose its integrity. Hopefully it will be fixed soon.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Sep 11, 2007

I am going to try to work through this. I want to say thank you for all your help....you have an abundance of patience and a wealth of knowledge. It is much appreicated. If I have any additional questions I will open a new thread. Thanks again.

Chopstik · Sep 11, 2007

Not a problem. We all have to learn sometime and I often end up learning new things as I help others, so it's often mutually beneficial. Once you feel comfortable, you will also be able to help others. [thumbsup]

Oh, and I wish I had an abundance of knowledge... ;-)

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Format Date 1

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

Similar threads

Part and Inventory Search

Sponsor