Changing Date Format

[munklefish]

Hi, Due to various limitation i need to be able to change the format of dates in a SQl2005 db from DD/MM/YYYY to YYYY/MM/DD. Ideally this should be a stored proceedure. Any help would as always be very greatfully received. Thanks in advance. Phill

[Taurian110]

Are you talking about updating the date format the way it is stored in the database? Or when you are retrieving it? Also what is the data type of your date column is it varchar or DateTime? If you just want to change the date format when retrieving the data then something like this will work:

SELECT
         Convert(varchar, DATEPART(yyyy, UploadDate)) + '/' + 
         Convert(varchar, DATEPART(mm, UploadDate)) + '/' + 
         Convert(varchar, DATEPART(dd, UploadDate)) as MyNewFormat
FROM
         dbo.[MytableName]

*Change the MytableName to your table and UploadeDate to your column name hth

[Blue_Boy]

select convert(varchar,columnname,111) from myTable

---

I Love T-SQL

[munklefish]

Its throwing up an OleDbException: 'Undefined function 'Convert' in expression.' Any ideas?

[Blue_Boy]

Are you using SQL Server database or MS Access database?

---

I Love T-SQL

[munklefish]

SQL 2005 Although im trying to do the convert on when uploading an Excel sheet to SQL2005 using BulkCopy. See below: protected void Page_Load(object sender, EventArgs e) { // Connection String to Excel Workbook //string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BLAH\qs_upload.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BLAH\qs_upload.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; // Create Connection to Excel Workbook using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand("Select * FROM [quality_standard$]", connection); //OleDbCommand command = new OleDbCommand("Select qs_Id, qs_Organisation, Convert(varchar,qs_Start,111), qs_End FROM [quality_standard$]", connection); connection.Open(); // Create DbDataReader to Data Worksheet using (OleDbDataReader dr = command.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = "REMOVED" // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "tbl_QualityStandard"; bulkCopy.WriteToServer(dr); } } } }