Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 14th, 2013, 02:42 PM
Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default using a date convert in union query

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.
 
Old June 15th, 2013, 03:24 AM
Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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???

Last edited by stevec6832; June 15th, 2013 at 03:27 AM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Parameter in a Union Query bright_mulenga Access VBA 8 January 25th, 2008 08:13 AM
Date Parameter in a Union Query bright_mulenga Access VBA 0 January 17th, 2008 03:52 AM
UNION QUERY Help Corey Access 1 October 27th, 2006 05:29 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.