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.

 

[Sheco]

Oh, LOL!

Well that would explain it if your recordset is named rs instead of oRS

 

[steven290]

by the way sheco nice function (could the same effect been done with formatdatetime?)

 

[Sheco]

Ah that was JSpicolli's function, my only suggestion was to move the function definition out from inside the While loop.

 

[steven290]

duh.. should have read further up

 

[jennypretty]

the Jen_begin_date part worked, but when I added & "-" & myshortdate2(rs("jen_end_date")), then It generate that error.
I think jen_begin_date and jen_end_date have the same RS.

Jenny.

 

[steven290]

i think the problem is in you sql try this

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

 

[Sheco]

Is this the same query where you wanted a fixed string as part of the recordset?

 

[steven290]

the function seems happen at the display level not the sql level, so the &"-"& in the sql is not needed

 

[Sheco]

In the other thread you did something like this:

rptStr = "select Ref_Date_Id, Cast(Ref_Begin_Date as varchar(50)) + ' - ' + Cast(Ref_End_Date as varchar(50)) as MyDate"

The purpose was to create a recordset with 2 columns: Ref_Date_ID and MyDate.

The value of MyDate was a string of the form "start - end"

Are you trying to do the same thing here Jenny?

 

[Sheco]

IE: Using the database to create formatted data?

 

[steven290]

lets not confuse her

this worked for her

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

then she asked

...How do I modify so that I [red]display[/red] like my query above like Jan 1 2004 - April 2005

Jen_Begin_Date & ' - ' & Jen_End_Date

so this should do the trick

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

with this sql

rptStr = "select Jen_Date_Id, Jen_begin_date, Jen_End_Date from Jen_Date"
rptStr = rptStr & " order by Jen_Date_Id Asc"

 

[Sheco]

I thought it would be easier to understand if she did this one the same way she did it in her other thread...

There she used the database to create a display formatted recordset rather than the traditional method of including the needed fields in the recordset and then formatting them in code.

It is 6 of one or half dozen of the other but I thought it would help her to do it the same as before is all.

 

[steven290]

k, well lets see what she says

 

[Sheco]

There is a & missing from the example above... right under the text "this should do the trick

 

[steven290]

good catch ;-)

 

[jennypretty]

I think this:
rptStr = "select Ref_Date_Id, Cast(Ref_Begin_Date as varchar(50)) + ' - ' + Cast(Ref_End_Date as varchar(50)) as MyDate"
is the simplest way to go.

The data display on the web is like this "Jan 2 2004 12:00AM-Jan 8 2005 12:am"

I don't want the time to show up there, just date.

Can you help to modify my CAST function to show just the date?

Right now, I am confused about the MyShortDate function.

Jenny.

 

[Sheco]

Yes if you use the CONVERT function instead of CAST.

The third (and optional) parameter to CONVERT will allow you to do this. See the SQL Server docs for specifics.

Alternatively you could bring the fields down from the database to your script and format them in ASP.

 

[steven290]

like said your sql doesn't need to have that - look try this out in a sep page

<%
str1="Jan 2 2004 12:00AM"
str2="Jan 8 2005 12:00am"
Function MyShortDate2(input)
  Dim sMonth
  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)
      sYear = DatePart("yyyy", input)
                
      sReturn = sMonth & " " & sYear
      MyShortDate2 = sReturn
  End IF
End Function

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 myshortdate(str1) & " - " & myshortdate2(str2)
%>

 

[steven290]

thats what the myshortdate function is for, to fix it for display

 

[Sheco]

Oh yeah, believe me I understand. I'd rather see it pulled down and formatted in ASP. That's how I'd do it.

But to do it like she had before it would be something like this:

select Ref_Date_Id, convert(varchar(50), Ref_Begin_Date, 101) + ' - ' + convert(varchar(50), Ref_End_Date , 101) as MyDate from Jen_Date