how to change date format in a select statement

when i use this command in a aspx file

"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.......................

'Format$' is not a recognized function name.

so how do i change date from mm/dd/yyyy to mm/yyyy

Check out the CAST and CONVERT functions in SQL BOL. They have a listing of all the possible combinations of formatting you can do for datetime values.|||

Hi~

Try this:

SELECTRIGHT(CONVERT(VARCHAR(10), Column_Name, 103), 7)AS [MM/YYYY]from Table_Name

Hope it helps.

How to call a Store Procedure

I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.

Pass the name of the stored procedure to a command object and set the CommandType to StoredProcedure. Then use ExecuteNonQuery to run it.

string query = "nameofsp";
string connect = "connectionstring";
using (SqlConnection conn = new SqlConnection(connect))
{
 using (SqlCommand cmd = new SqlCommand(query, conn))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 conn.Open();
 cmd.ExecuteNonQuery();
 }
}

|||

Thanks for the quick reply.

I should have mentioned I'm building the apsx page with vb. How different will that look from the above?

Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?

Thanks!

|||

Dim dbobj as new SQLConnection(connection_string_here)

dbobj.open()

Dim objCmd as new SQLCommand(sp_name_here, dbobj)

objCmd.CommandType = CommandType.StoredProcedure

objCmd.Parameters.Add("@.Param_Name_Here", Param_Value_Here) 'if any

objCmd.ExecuteNonQuery() 'Use ExecuteScalar if you want to return a single value

objCmd.Dispose()

dbobj.Close()

I would create the procedure first to run the DTS and call it as above.

|||

tomhirt:

I should have mentioned I'm building the apsx page with vb.

That would have been helpful, but not particularly necessary if you just copy and paste the C# code I gave into www.codechanger.com

tomhirt:

How different will that look from the above?

Almost the same:

Dim query As String = "nameofsp"
Dim connect As String = "connectionstring"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using

I tend to use Using statements for objects that need to be closed and disposed. Saves me remembering to have to do it explicitly each time.

tomhirt:

Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?

Thanks!

Like Rich said.

|||

Excellent. I did not know I could use something like Code changer. Great refernce.