[2005] Date format

[bomayed]

I have two questions to ask :

I had a field set as (date/time) in my MS Access database

I placed a drop down list on my page and added a datasource. what I wanted to do is to display in the drop down list :

1-Only unique dates and order them by day / month / year
2-Ignore the time

So my statement was :

SELECT DISTINCT MyDateField from table order by MyDateField asce

What I'm getting is a list of distinct dates which great, but each date is followed by time : AM 12:00:00

any idea how I can get red of the time !

Secondly the date doesn't appear correct , it changes the day to month and month to day like:

1/12/2007 is changed to 12/1/2007

any idea also why this happened ?

[Lord Orwell]

The date/time field stores both at the same time. You need to set the format property of the combo box to something similar to MM/DD/YYYY

[Harsh Gupta]

To get rid of Time, use DateTime.ToShortDateString() method

[bomayed]

How can I use DateTime.ToShortDateString() method when I'm binding data from access file to a gridview ?


I went to the format string of the dropdownlist and I changed it to DataTextFormatString=" {0:dd/MM/yyyy}"

Still it's changing the day to month and month to day , it's not taking the date exactlly as it appears on the access file !

[Harsh Gupta]

How can I use DateTime.ToShortDateString() method when I'm binding data from access file to a gridview ?

Why?? It isn't that hard!!

And are you binding it with the Dropdownlist or GridView??

For DDL, on the Prerender event of page, get individual item, and pass it through the ToShortDateString() method.

For GV, on the RowDataBound event, get the date's cell item and pass it through the ToShortDateString() method.

[Lord Orwell]

Check the format on the table. There is no reason for it to be switching them around unless someone set the format of the datasheet column to dd/mm/yyyy.

[bomayed]

I have just realized after that the dates were inserted in the wrong order in the first place

so take that into consideration when you face this issue . .

[Lord Orwell]

ok. Mark resolved, thanks!

[brin351]

Date format - mm/dd/yy is the USA date format which Microsoft uses accross the board. This causes a potential problem when lots of other countries use dd/mm/yy

So when INSERTING dates into a database (access/ mssql) always format them yyyy/mm/dd - dont ask me why but this always works. You can use dd/month name / yyyy but that's a problem. If you uses dd/mm/yy the day and month will be swapped if the day is 12 or less (i.e. - months) or if the day is > 12 it will not be ---- so you end up with a mess of muddled dates.

When you get dates out of your DB know they will always be in mm/dd/yy format.

If you need to group by dates but not date and time Access has a date only dataType that you can use for the table column sqlServer does not, or you can just insert a date and the time of all dateTimes will be 12.00.00am so it works out the same any way.

If you need the date and time and need to group by date only in sqlServer you can do SELECT DISTINCT(dateadd(day,datediff(day,(0),COLUMN_NAME),(0))) bla....

or SELECT bla.... GROUP BY (dateadd(day,datediff(day,(0),COLUMN_NAME),(0)))

It should also work in access? - It is better performance to have your database return what you need than altering records in code.

[Lord Orwell]

You can also store the data in the database in text format and format it as a date. My preferred use is dd/mmm/yyyy. This is the standard the U.S. government uses.