Hi, I am using DNN reports module but am having a bit of difficulty in converting dates in a union query. I know maybe this is a dnn module question but in trying to resolve it I cant get it to work on SQL in the first instance.
Basically the query is only looking at the one table of which I have several columns all datetime, when run everything works fine but the output is mm/dd/yyyy and I would like to change it to dd/mm/yyyy.
Running this on a single column works but in a union query it just restes back to mm/dd/yyyy
This is the query I am using:
select [col1], [col2],[col3],[col4],[col5] as 'Report1', (select convert (datetime,[date1],103)) AS [Date Submitted], [col5] as 'Current Review Status'
where [date1] >dateadd(week,-1,getdate())
and [col5] is not null
select [col1], [col2],[col3],[col4],[col8] as 'Reprort2',(select convert (datetime,[date2],103)) AS [Date Submitted], [col9]
where [date2] >dateadd(week,-1,getdate())
and [col7] is not null
I can also add grouping but not important right now. All cols are the same data type in the union.
The query runs fine but both of the date fields come back as example 2013-06-13 00:00:00.000
I want to just get a date back as dd/mm/yyyy
I have also tried the cast option e.g
SELECT CAST([date] AS DATE) AS 'date'
replacing it with the convert as in my code. Again on a single query the cast works fine e.g 2012-10-29 but when in a union query nothing happens?
As mentioned I am doing this to modify an output in a dnn reports module but want to just get it working in SQL first. As a footnote when run using the convert in DNN reports module the output is e.g 10/29/2012 12:00:00 AM even though my site set to UK settings.