convert date/time in database code

Question

Hi all I have a combo box that is populated by a table of date/time. I have a Ms Access Database and i have OleDBadaptor with a sql statement SELECT date from date1. This populates the ComboBox with a date/time data but i would like to filter out the time. Does anyone know how to do this within the sql statement. I tried an oracle sql statement select to_char(Date 'ddmmyyyy') from Date1 it does not work. I also used a transact sql statement select date = convert(char(15)date110) from Date1 and that does not work. Is the sql that is used in C# Transact Sql or some other fragment of sql. thanks kamean

yasser_8227 · Answer

It might be a data problem. However you can get the date and displayed it in your desired format using C# DateTime parser. For example: if(ds.Tables[0].Rows[0][0] != null && ds.Tables[0].Rows[0][0].ToString() != ) { txtDate.Text = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString()).ToString(dd/MM/yyyy); } On more format strings check this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp This should work fine. If this is not working then problem is with your data. Cheers Yasser

djohncon · Answer

Hi Yasser thanks for your reponse to my problem. I am running this connection through a dataset. I tested it with select DateValue(DDT) from DateTable; This seems to work fine . Compiler runs without error but if I try to run the program it raises an error. An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll Additional information: Failed to enable constraints. One or more rows contain values violating non-null unique or foreign-key constraints. I cannot find what it is looking for? I thought that the Database had a duplicate at first but that was not the case all dates are unique. Any clues kamean

yasser_8227 · Answer

Hi You'll need to use Date/Time functions supported by MS Access. To display date you can either use DateValue(date): In your case: SELECT DateValue(date) from date1 or FormatDateTime(date NamedFormat): In your case: Select FormatDateTime(date 2) For more info on these functions check this out: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctformatdatetime.asp Cheers Yasser