get minimum date.

[Nekshan]

i have this in database: accessdate 5/2/2007 3/2/2007 2/3/2007 8/3/2007 2/4/2007 2/5/2007 5/5/2007 Now want to access minimum date from it : SELECT MIN(accessdate) FROM tblsummary i shud get : 5/2/2007(as minimum), But i m getting : 2/3/2007(as it is just comparing 2<5 ie date, but not month which is 2<3) Same is happening for max(accessdate) too. How can i rectify it,it wud b pleasure if some1 can help. Thank You in adv. Regards Nekshan.

[virendra patel]

SELECT MIN(CONVERT(varchar, DateCreated, 111)) FROM tblsummary

[Guffa]

Please don't use txtspk. This is a forum, not a text message. Store the dates as dates, not as text. Otherwise you have to convert each value to date in the query to get the aggregate to compare them as dates.

--- single minded; short sighted; long gone;

[Nekshan]

SELECT MIN(CONVERT(varchar, DateCreated, 111)) FROM tblsummary error in query analyzer: Invalid Columnname 'DateCreated'. 'accessdate' column in 'tblsummary'table has 'datetime' datatype. Did u understand my problem in last post? My query is not checking month. Does 'datetime' datatype takes data in database as 'mm/dd/yyyy'? I inserted data as 'dd/mm/yyyy'.

[virendra patel]

date is stored in database in only one way. u have to fatch date as u want in any format. y'r prob solved with above posted ans. query .

[Nekshan]

yes my problem is solved. datetime datatype stores data in mm/dd/yyyy format. now its working. thank you. :)

[virendra patel]

which query u r using for fetch min date from database.