Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990
-
Hi all, When I exporting Database table to Excel my date fields are giving 23/01/1990 00:00:00 in stat of 23/01/1990 Please can some one help me in the right way.
Quote:
protected DataTable ExportDataFromSQLServer() { DataTable dataTable = new DataTable(); using (SqlConnection connection = new SqlConnection(@"Data Source= 127.0.0.1\MSSQLSERVERJAN; Initial Catalog=BHV; User ID=sa;Password=XXXXXXX")) { connection.Open(); // Define the query to be performed to export desired data SqlCommand command = new SqlCommand("select * from BHVUsers", connection); SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dataTable); var excelApplication = new Excel.Application(); var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing); DataColumnCollection dataColumnCollection = dataTable.Columns; for (int i = 1; i <= dataTable.Rows.Count + 1; i++) { for (int j = 1; j <= dataTable.Columns.Count; j++) { if (i == 1) excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString(); else excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString(); } } excelApplication.Cells[1, 2] = "Voornaam"; excelApplication.Cells[1, 3] = "Achternaam"; excelApplication.Cells[1, 4] = "Geboorte Datum"; excelApplication.Cells[1, 5] = "Porto nummer"; excelApplication.Cells[1, 6] = "EHBO behaald"; excelApplication.Cells[1, 7] = "EHBO"; excelApplication.Cells[1, 8] = "BHV"; excelApplication.Cells[1, 9] = "Regisseur"; excelApplication.Cells[1, 10] = "Verlopen EHBO"; excelApplication.Cells[1, 11] = "Laatste herhaling EHBO"; excelApplication.Cells[1, 12] = "EHBO nummer"; excelApplication.Cells[1, 13] = "Verdieping"; excelApplication.Cells[1, 14] = "Ruimte"; excelApplication.Cells[1, 15] = "Email"; excelApplication.Cells[1, 16] = "Mobiel"; excelApplication.Cells[1, 17]
-
Hi all, When I exporting Database table to Excel my date fields are giving 23/01/1990 00:00:00 in stat of 23/01/1990 Please can some one help me in the right way.
Quote:
protected DataTable ExportDataFromSQLServer() { DataTable dataTable = new DataTable(); using (SqlConnection connection = new SqlConnection(@"Data Source= 127.0.0.1\MSSQLSERVERJAN; Initial Catalog=BHV; User ID=sa;Password=XXXXXXX")) { connection.Open(); // Define the query to be performed to export desired data SqlCommand command = new SqlCommand("select * from BHVUsers", connection); SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dataTable); var excelApplication = new Excel.Application(); var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing); DataColumnCollection dataColumnCollection = dataTable.Columns; for (int i = 1; i <= dataTable.Rows.Count + 1; i++) { for (int j = 1; j <= dataTable.Columns.Count; j++) { if (i == 1) excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString(); else excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString(); } } excelApplication.Cells[1, 2] = "Voornaam"; excelApplication.Cells[1, 3] = "Achternaam"; excelApplication.Cells[1, 4] = "Geboorte Datum"; excelApplication.Cells[1, 5] = "Porto nummer"; excelApplication.Cells[1, 6] = "EHBO behaald"; excelApplication.Cells[1, 7] = "EHBO"; excelApplication.Cells[1, 8] = "BHV"; excelApplication.Cells[1, 9] = "Regisseur"; excelApplication.Cells[1, 10] = "Verlopen EHBO"; excelApplication.Cells[1, 11] = "Laatste herhaling EHBO"; excelApplication.Cells[1, 12] = "EHBO nummer"; excelApplication.Cells[1, 13] = "Verdieping"; excelApplication.Cells[1, 14] = "Ruimte"; excelApplication.Cells[1, 15] = "Email"; excelApplication.Cells[1, 16] = "Mobiel"; excelApplication.Cells[1, 17]
Did you try to set the cell format as DATE, not DateTime?
-
Hi all, When I exporting Database table to Excel my date fields are giving 23/01/1990 00:00:00 in stat of 23/01/1990 Please can some one help me in the right way.
Quote:
protected DataTable ExportDataFromSQLServer() { DataTable dataTable = new DataTable(); using (SqlConnection connection = new SqlConnection(@"Data Source= 127.0.0.1\MSSQLSERVERJAN; Initial Catalog=BHV; User ID=sa;Password=XXXXXXX")) { connection.Open(); // Define the query to be performed to export desired data SqlCommand command = new SqlCommand("select * from BHVUsers", connection); SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dataTable); var excelApplication = new Excel.Application(); var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing); DataColumnCollection dataColumnCollection = dataTable.Columns; for (int i = 1; i <= dataTable.Rows.Count + 1; i++) { for (int j = 1; j <= dataTable.Columns.Count; j++) { if (i == 1) excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString(); else excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString(); } } excelApplication.Cells[1, 2] = "Voornaam"; excelApplication.Cells[1, 3] = "Achternaam"; excelApplication.Cells[1, 4] = "Geboorte Datum"; excelApplication.Cells[1, 5] = "Porto nummer"; excelApplication.Cells[1, 6] = "EHBO behaald"; excelApplication.Cells[1, 7] = "EHBO"; excelApplication.Cells[1, 8] = "BHV"; excelApplication.Cells[1, 9] = "Regisseur"; excelApplication.Cells[1, 10] = "Verlopen EHBO"; excelApplication.Cells[1, 11] = "Laatste herhaling EHBO"; excelApplication.Cells[1, 12] = "EHBO nummer"; excelApplication.Cells[1, 13] = "Verdieping"; excelApplication.Cells[1, 14] = "Ruimte"; excelApplication.Cells[1, 15] = "Email"; excelApplication.Cells[1, 16] = "Mobiel"; excelApplication.Cells[1, 17]
Usually you don't include the password for the sa-account in the question. Usually we also frown on "SELECT *", and weirdly, you use a "using" clause for the connection but not for the disposable command.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Hi Richard, I was trying that with no results. Do yoy have a solution how to do this. Thanks, Regards, Jan Meeling
-
Did you try to set the cell format as DATE, not DateTime?
Hi Victor, Thanks for your quick response. Do you have the phrase How I have to implement. Thanks Regards, Jan Meeling
-
Usually you don't include the password for the sa-account in the question. Usually we also frown on "SELECT *", and weirdly, you use a "using" clause for the connection but not for the disposable command.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Hi Bastard, I have removed the password and this is only in a test enviroment, And what do you mean with Usually we also frown on "SELECT *", and weirdly, you use a "using" clause for the connection but not for the disposable command. Thanks Regards Jan Meeling
-
Hi Bastard, I have removed the password and this is only in a test enviroment, And what do you mean with Usually we also frown on "SELECT *", and weirdly, you use a "using" clause for the connection but not for the disposable command. Thanks Regards Jan Meeling
jan Meeling wrote:
Usually we also frown on "SELECT *",
A select all, versus a select where you only select what you will use. You're now fetching more than expected and this may have an impact on speed.
jan Meeling wrote:
and weirdly, you use a "using" clause for the connection but not for the disposable command.
You put the connection in a using-block, but not the command. Any reason why?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Hi Richard, I was trying that with no results. Do yoy have a solution how to do this. Thanks, Regards, Jan Meeling
Have a look at [excel - VBA code to set date format for a specific column as "yyyy-mm-dd" - Stack Overflow](https://stackoverflow.com/questions/12324190/vba-code-to-set-date-format-for-a-specific-column-as-yyyy-mm-dd)
-
Hi Richard, I was trying that with no results. Do yoy have a solution how to do this. Thanks, Regards, Jan Meeling
-
jan Meeling wrote:
Usually we also frown on "SELECT *",
A select all, versus a select where you only select what you will use. You're now fetching more than expected and this may have an impact on speed.
jan Meeling wrote:
and weirdly, you use a "using" clause for the connection but not for the disposable command.
You put the connection in a using-block, but not the command. Any reason why?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Hi all, I have it working know. The solution is in the SQL string. SELECT ID_BHV_Users, Voornaam , Achternaam , FORMAT (Geboorte_Datum, 'dd/MM/yyyy'), Porto_Nummer , Format (EHBO_Behaald, 'dd/MM/yyyy') , EHBO , BHV , Regiseur , Format ( Verlopen, 'dd/MM/yyyy') , Format (Laatste_herhaling, 'dd/MM/yyyy') , EHBO_nummer , verdieping , Ruimte , Email , Mobiel , Opmerking , Format (BHV_Behaald, 'dd/MM/yyyy') , Format (BHV_Verlopen, 'dd/MM/yyyy') , Format (Laatste_Herhaling_BHV, 'dd/MM/yyyy') , Ruisnaam , Format (Regisseur_Verlopen, 'dd/MM/yyyy') , Format (Regisseur_Behaald, 'dd/MM/yyyy') , Format (Regisseur_Herhaling, 'dd/MM/yyyy') , Active_no_yes FROM BHVUsers" I use the format in the SQL string You All thanks for putting me in the right direction. Thanks Jan Meeling :) ;)
-
Hi all, When I exporting Database table to Excel my date fields are giving 23/01/1990 00:00:00 in stat of 23/01/1990 Please can some one help me in the right way.
Quote:
protected DataTable ExportDataFromSQLServer() { DataTable dataTable = new DataTable(); using (SqlConnection connection = new SqlConnection(@"Data Source= 127.0.0.1\MSSQLSERVERJAN; Initial Catalog=BHV; User ID=sa;Password=XXXXXXX")) { connection.Open(); // Define the query to be performed to export desired data SqlCommand command = new SqlCommand("select * from BHVUsers", connection); SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dataTable); var excelApplication = new Excel.Application(); var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing); DataColumnCollection dataColumnCollection = dataTable.Columns; for (int i = 1; i <= dataTable.Rows.Count + 1; i++) { for (int j = 1; j <= dataTable.Columns.Count; j++) { if (i == 1) excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString(); else excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString(); } } excelApplication.Cells[1, 2] = "Voornaam"; excelApplication.Cells[1, 3] = "Achternaam"; excelApplication.Cells[1, 4] = "Geboorte Datum"; excelApplication.Cells[1, 5] = "Porto nummer"; excelApplication.Cells[1, 6] = "EHBO behaald"; excelApplication.Cells[1, 7] = "EHBO"; excelApplication.Cells[1, 8] = "BHV"; excelApplication.Cells[1, 9] = "Regisseur"; excelApplication.Cells[1, 10] = "Verlopen EHBO"; excelApplication.Cells[1, 11] = "Laatste herhaling EHBO"; excelApplication.Cells[1, 12] = "EHBO nummer"; excelApplication.Cells[1, 13] = "Verdieping"; excelApplication.Cells[1, 14] = "Ruimte"; excelApplication.Cells[1, 15] = "Email"; excelApplication.Cells[1, 16] = "Mobiel"; excelApplication.Cells[1, 17]
Hi all, I have it working know. The solution is in the SQL string. SELECT ID_BHV_Users, Voornaam , Achternaam , FORMAT (Geboorte_Datum, 'dd/MM/yyyy'), Porto_Nummer , Format (EHBO_Behaald, 'dd/MM/yyyy') , EHBO , BHV , Regiseur , Format ( Verlopen, 'dd/MM/yyyy') , Format (Laatste_herhaling, 'dd/MM/yyyy') , EHBO_nummer , verdieping , Ruimte , Email , Mobiel , Opmerking , Format (BHV_Behaald, 'dd/MM/yyyy') , Format (BHV_Verlopen, 'dd/MM/yyyy') , Format (Laatste_Herhaling_BHV, 'dd/MM/yyyy') , Ruisnaam , Format (Regisseur_Verlopen, 'dd/MM/yyyy') , Format (Regisseur_Behaald, 'dd/MM/yyyy') , Format (Regisseur_Herhaling, 'dd/MM/yyyy') , Active_no_yes FROM BHVUsers" I use the format in the SQL string You All thanks for putting me in the right direction. Thanks Jan Meeling Smile | :) Wink | ;)
-
Hi all, I have it working know. The solution is in the SQL string. SELECT ID_BHV_Users, Voornaam , Achternaam , FORMAT (Geboorte_Datum, 'dd/MM/yyyy'), Porto_Nummer , Format (EHBO_Behaald, 'dd/MM/yyyy') , EHBO , BHV , Regiseur , Format ( Verlopen, 'dd/MM/yyyy') , Format (Laatste_herhaling, 'dd/MM/yyyy') , EHBO_nummer , verdieping , Ruimte , Email , Mobiel , Opmerking , Format (BHV_Behaald, 'dd/MM/yyyy') , Format (BHV_Verlopen, 'dd/MM/yyyy') , Format (Laatste_Herhaling_BHV, 'dd/MM/yyyy') , Ruisnaam , Format (Regisseur_Verlopen, 'dd/MM/yyyy') , Format (Regisseur_Behaald, 'dd/MM/yyyy') , Format (Regisseur_Herhaling, 'dd/MM/yyyy') , Active_no_yes FROM BHVUsers" I use the format in the SQL string You All thanks for putting me in the right direction. Thanks Jan Meeling :) ;)
-
Well that is even worse than where you started from. Do not use formatted date strings but use proper date values and Date types. Also use proper parameterised queries to avoid SQL injection problems.
Hi Richard, Thanks for reaction. Can you give me the right way how to do is. I now about SQL injection problems but this not connected to the internet and only in my test environment. Thanks, regards, Jan Meeling :) :)
-
Hi Richard, Thanks for reaction. Can you give me the right way how to do is. I now about SQL injection problems but this not connected to the internet and only in my test environment. Thanks, regards, Jan Meeling :) :)