change stored date format in database

Question

How do I change the format in which the date is stored in a database from mm/dd/yyyy to dd/mm/yyyy? As the MSHFlexGrid reads data directly from the database, its source, need to store the Date in dd/mm/yyyy format.There is no problem in reading mm/dd/yyyy in textbox as dd/mm/yyyy.Please Help!

Answer 1

Dates are generally stored in the database in an agnostic form, they are only presented in localised formats. You need to look at the localisation setting for you system/component and/or modify your SELECT query to return the date in the format you require (although I suspect that the Grid will still present it in the US format).

Answer 2

Dates are stored as they are. You can't change that. You CAN, however, create a computed column and display the dates as a varchar by converting your date column to a varchar column in whatever format you need, and display that.

(It will be read-only in your form, though)

Conversion functions here: http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx

Computed column here: http://msdn.microsoft.com/en-us/library/ms191250.aspx

Answer 3

"the MSHFlexGrid reads data directly from the database"

Surely it must be using something in the middle e.g. DataEnvironment designer, ADODB Recordset, etc?

The 'H' in 'MSHFlexGrid' stands for hierarchical: are you showing hierarchical data e.g. binding a hierarchical ADODB Recordset to the grid's DataSource property?

If you don't need hierarchical data, consider the Microsoft DataGrid (MSDatGrd.ocx). You can bind a non-hierarchical ADODB Recordset to it and exploit its Columns' DataFormat properties, which are of type StdFormat.StdDataFormat i.e. have events for _Format and _UnFormat respectively. I use this to add subsecond precision to DATETIME values, replace NULL with '' etc.

UPDATE:

You may be able to create a data sink using the BindingCollection object (requires a reference to Microsoft Data Binding Collection VB 6.0) because a Binding object supports the DataFormat property. For a working example, see Data Sources, Data Sinks.

http://www.aivosto.com/visdev/data.html

Answer 4

I don't think changing the system date format is a complete solution because you can change computers and still have the same problem,the best and simplest way is by specifying the format you want in the query, i don't know what database you are using but i would have given you an example.

If the grid is reading directly from the table then you need to create a view querying from that table then specify the format there.