date format like Jan 11 2002 PLEASE HELP 1

[jennypretty]

Hello friends,
I am trying to format the date return and it generate this error: DateTimeFormat is not a recognized funtion name.

Select Cast(FormatDateTime(hiredate,2) as varchar(50)
from emp

The date format return I want is like this: Jan 11 2002.

I also tried this, Cast(FormatDateTime(hiredate,"mmm dd yyyy") as varchar(50)

Please help.
Thanks,
Jenny.

 

[DotNetGnat]

FormatDateTime is not a SQL function.

you need to use this function while displaying the date on your ASP page.

-DNG

 

[JSpicolli]

Dont format the date in your SQL, format it in ASP

Response.Write(MyShortDate(oRS("ref_BeginDate")))
		
		
		Function MyShortDate(input)
		
			Dim sMonthName
			Dim sDay
			Dim sYear
			
			Dim sReturn
			
			If Not isDate(cDate(input)) Then
				MyShortDate = "input param is not a Date type"
				Exit Function
			Else
				sMonth = MonthName(DatePart("m", input), True)
				sDay = DatePart("d", input)
				sYear = DatePart("yyyy", input)
				
				sReturn = sMonth & " " & sDay & " " & sYear
				MyShortDate = sReturn
				
			End IF
			
		End Function

 

[jennypretty]

I am newbie in asp. Do you know where do I put that in my page?
Thanks,
Jenny.

 

[DotNetGnat]

your page outline should look something like this...

'declare variables
'create connection object
'create recordset object
'open connection
'sql string
'execute your sql statement
'display the records----here is where you use the FormatDateTime function.
'close everything

Thanks

-DNG

 

[JSpicolli]

Jenny.


Put the function in the page anywhere.

Then Call the function whereever you would like to format a date in ASP.

 

[jennypretty]

I put exactly as you said, but errors occurred: "MS VBSCript compilation (0x800A03EA) syntax error
Function MyShortDate(input)
Can you help?

rptStr = "select Jen_Date_Id, Jen_begin_date, Jen_Begin_Date & ' - ' & Jen_End_Date from Jen_Date"
rptStr = rptStr & " order by Jen_Date_Id Asc"
Set rs = Server.CreateObject ("ADODB.Recordset")

rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
if (fyValue = 0) or (DatePart("YYYY",rs(1)) = Cint(fyValue)) then
Response.Write("<OPTION value=" & rs(0) & ">")

Response.Write(MyShortDate(oRS("Jen_Begin_Date")))


Function MyShortDate(input)

Dim sMonthName
Dim sDay
Dim sYear

Dim sReturn

If Not isDate(cDate(input)) Then
MyShortDate = "input param is not a Date type"
Exit Function
Else
sMonth = MonthName(DatePart("m", input), True)
sDay = DatePart("d", input)
sYear = DatePart("yyyy", input)

sReturn = sMonth & " " & sDay & " " & sYear
MyShortDate = sReturn

End IF

End Function
Response.Write(rs(2))
Response.Write("</OPTION>")

 

[Sheco]

Why is there a function declaration inside the While loop?


Just call it from inside the loop, put it elsewhere like at the beginning or end.

 

[Sheco]

more like this:

Function MyShortDate(input)
  Dim sMonthName
  Dim sDay
  Dim sYear
  Dim sReturn
            
  If Not isDate(cDate(input)) Then
      MyShortDate = "input param is not a Date type"
      Exit Function
  Else
      sMonth = MonthName(DatePart("m", input), True)
      sDay = DatePart("d", input)
      sYear = DatePart("yyyy", input)
                
      sReturn = sMonth & " " & sDay & " " & sYear
      MyShortDate = sReturn
  End IF
End Function

rptStr = "select Jen_Date_Id, Jen_begin_date, Jen_Begin_Date & ' - ' & Jen_End_Date from Jen_Date"
rptStr = rptStr & " order by Jen_Date_Id Asc"
Set rs = Server.CreateObject ("ADODB.Recordset")
    
rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
    if (fyValue = 0) or (DatePart("YYYY",rs(1)) = Cint(fyValue)) then
        Response.Write("<OPTION value=" & rs(0) & ">")
				Response.Write(MyShortDate(oRS("Jen_Begin_Date")))
        Response.Write(rs(2))
        Response.Write("</OPTION>")

 ....

 

[Maim]

Don't forget to declare your variables correctly, sMonthName != sMonth

-----------------------------------
&quot;Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.&quot; - Rich Cook

 

[Sheco]

ah yeah I didnt check for any other problems, i just moved the function declaration out of the loop.

 

[jennypretty]

Where do I declare sMonthname variable?
Jenny.

 

[steven290]

this part

Function MyShortDate(input)
Dim [red]sMonthName[/red]
Dim sDay
Dim sYear

should be

Function MyShortDate(input)
Dim [red]sMonth[/red]
Dim sDay
Dim sYear

 

[jennypretty]

type mismatch: 'oRS'
This line generate error:
Response.Write(MyShortDate(oRS("Jen_Begin_Date")))

Jenny.

 

[steven290]

change to this

Response.Write(MyShortDate(RS("Jen_Begin_Date")))

 

[jennypretty]

Hah! it worked. How do I modify so that I display like my query above like Jan 1 2004 - April 2005

Jen_Begin_Date & ' - ' & Jen_End_Date

Thanks,
Jenny.

 

[steven290]

i believe you and add a function like the one there

Function MyShortDate2(input)
  Dim sMonth
  Dim sYear
  Dim sReturn
            
  If Not isDate(cDate(input)) Then
      MyShortDate2 = "input param is not a Date type"
      Exit Function
  Else
      sMonth = MonthName(DatePart("m", input), True)
      sYear = DatePart("yyyy", input)
                
      sReturn = sMonth & " " & sYear
      MyShortDate2 = sReturn
  End IF
End Function

then

...
Response.Write MyShortDate(oRS("Jen_Begin_Date")) & "-" & MyShortDate2(oRS("Jen_End_Date"))
...

 

[jennypretty]

I tried it, but it generate errors: "Item cannnot be found in the collection corresponding to the requested name or ordinal."
This line:
Response.Write MyShortDate(oRS("Jen_Begin_Date")) & "-" & MyShortDate2(oRS("Jen_End_Date"))
Jenny.

 

[Sheco]

That error message given that line of code means that it didnt find a field in your recordset named either Jen_Begin_Date or Jen_End_Date

 

[steven290]

Response.Write MyShortDate(RS("Jen_Begin_Date")) & "-" & MyShortDate2(RS("Jen_End_Date"))