using a date convert in union query

stevec6832 · June 14th, 2013, 02:42 PM

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:

Code:

select [col1], [col2],[col3],[col4],[col5] as 'Report1', (select convert (datetime,[date1],103)) AS [Date Submitted], [col5] as 'Current Review Status'
from [dbo].[mytable]
where [date1] >dateadd(week,-1,getdate())
and [col5] is not null
union
select [col1], [col2],[col3],[col4],[col8] as 'Reprort2',(select convert (datetime,[date2],103)) AS [Date Submitted], [col9]
from [dbo].[mytable]
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

Code:

SELECT  CAST([date] AS DATE) AS 'date'
from [dbo].[mytable]

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.

stevec6832 · June 15th, 2013, 03:24 AM

Just an update I have now got this working, for those who have read this I found in DNN how to set localization which let me return the dates as dd/mm/yyyy so did not need to convert. I then used the cast function to shorten the date. This works in query anayliser fine but not on my site.
It was not working in the union query before due to the fact I had enclosed the function in (), when I took them off it worked fine.
eg.

Quote:

select [col1], [col2],[col3],[col4],[Col5] as 'Report', CAST([col6] AS DATE) AS 'Submitted Date', [Col9]
from [dbo].[my table]
where [col6] >dateadd(month,-1,getdate())
and [Col5] is not null
union
select [col1], [col2],[col3],[col4],[Col7] as 'Report', CAST([col8] AS DATE) AS 'Submitted Date', [Col10]
from [dbo].[mytable]
where [col8] >dateadd(month,-1,getdate())
and [Col7] is not null

and not

Quote:

select [col1], [col2],[col3],[col4],[Col5] as 'Report', (select CAST([col6] AS DATE) AS 'Submitted Date'), [col9]
from [dbo].[mytable]
where [col6] >dateadd(month,-1,getdate())
and [Col5] is not null
union
select [col1], [col2],[col3],[col4],[Col7] as 'Report', (select CAST([col8] AS DATE) AS 'Submitted Date'), [Col10]
from [dbo].[mytable]
where [col8] >dateadd(month,-1,getdate())
and [Col7] is not null

No longer relevant for this area but a VB.net issue is that this works fine on query analyser but wont run the cast on a VB asp page as still get the time part showing, not sure but think in may be format I need now???