1. 

   -nth-, Member

   I've got several reports that have date columns. The SQL column definition is DATE. In preferences, I've got the date format set to "mm/dd/yyyy". When I run a report the date is displayed as "yyyy-mm-dd" and if I sort the date column (by clicking it's header) it doesn't sort ascending or descending by date. I'm sure it's just a setting I'm missing somewhere...

   Thanks!
   -nth-
2. 

   myDBR Team, Key Master

   How does your query look? Sounds like you fetching a string instead of a date.

   --
   myDBR Team
3. 

   -nth-, Member

   query is fairly plain vanilla:

   
   
   select 	a.Sdate as 'Service Date'
   
   ...
   
   where a.Sdate between @StartDate and @EndDate
   
   

   The thing is the where clause behaves as it should. It selects the correct date range, it's just in the report display that it doesn't show up properly.

   Back end is MSSQL2008.

   Here's the column as normal:
   

   Here's the column sorted descending:

   

   Sorted ascending:

   

   So something isn't quite working right.
4. 

   myDBR Team, Key Master

   OK,
   in SQL Server the freetds extension does not return the dataype of 'date' correctly (it says it's a string). In order to help myDBR to detect correct datatype you need to add ':type=date' into the column name.

   select a.Sdate as 'Service Date:type=date'
   
   ...
   
   where a.Sdate between @StartDate and @EndDate
   
   

   --
   myDBR Team
5. 

   -nth-, Member

   Yep, that worked.

   With all the extra little things I'm having to do with MSSQL it makes me wonder if I should rebuild on top of Mysql... :)
6. 

   myDBR Team, Key Master

   Not really ;). This is pretty much the only extra thing you need to remember.

   MySQL has it's own limitations. SQL Server is a very good database and T-SQL has clear advantages.

   --
   myDBR Team

Reply

You must log in to post.