Union of 3 columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union of 3 columns

Author

Topic

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 08:29:05

how can I cast 3 column into one that has the data type Datetime ?
1 column = Day
2 column = Month
3 column = Year

I need to union these columns into one column : dd-mm-yyyy

10x !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 08:33:04

SELECT DATEADD(MONTH, 12 * [Year] + [Month] - 22801, [Day] - 1)

E 12°55'05.63"
N 56°04'39.26"

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 08:37:14

Returns a new datetime value by adding an interval to the specified datepart of the specified date.

i need to copy the values from my 3 columns into one that unites those 3 as datetime :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 08:38:56

ALTER TABLE YourTableNameHere
ADD [Date] AS (DATEADD(MONTH, 12 * [Year] + [Month] - 22801, [Day] - 1))

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 08:40:56

select convert(datetime,'1'+'-'+'23'+'-'+'2009')

select convert(datetime,monthcolumnname+'-'+daycolumnname+'-'+yearcolumnname)
from tablename

if there fields are varchar

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 08:44:34

it over flows the output value , because the 3 columns are char not varchar , should i cast them as varchar and then select them ?

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 08:51:54

select convert(datetime,[day]+'-'+[month]+'-'+[year]) from

(
select convert(varchar,redat ) [day] ,
convert(varchar,redam )[month],
convert(varchar,redat ) [year]
from lqdandebrr
) x

error msg : Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 09:48:32

anyone ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 09:55:36

See my answers made 04/23/2009 : 08:33:04 and 04/23/2009 : 08:38:56?
What's wrong with them?

E 12°55'05.63"
N 56°04'39.26"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-23 : 09:57:20

try this..

declare @t table ([day] int, [month] int, [year] int)

insert @t
select 12,12,2009

select convert(datetime,((convert(varchar,[year])) + '-' + (convert(varchar,[month])) + '-' + convert(varchar,[day]))) from @t

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 10:00:29

quote:
Originally posted by Peso

See my answers made 04/23/2009 : 08:33:04 and 04/23/2009 : 08:38:56?
What's wrong with them?

E 12°55'05.63"
N 56°04'39.26"

it completed succ , only when i open the table it gave me a error msg , canot select from date ( dateadd compute , overflow)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 10:04:54

Works great for me

SELECT		theYear,
		theMonth,
		theDay,
		DATEADD(MONTH, 12 * theYear + theMonth - 22801, theDay - 1) AS Peso
FROM		(
			SELECT	2000 + Number AS theYear
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number BETWEEN 0 AND 9
		) AS y
CROSS JOIN	(
			SELECT	Number AS theMonth
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number BETWEEN 1 AND 12
		) AS m
CROSS JOIN	(
			SELECT	Number AS theDay
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number BETWEEN 1 AND 28
		) AS d
ORDER BY	theYear,
		theMonth,
		theDay

E 12°55'05.63"
N 56°04'39.26"

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-23 : 10:06:41

quote:
Originally posted by vijayisonly

try this..

select convert(datetime,((convert(varchar,[year])) + '-' + (convert(varchar,[month])) + '-' + convert(varchar,[day]))) from @t

it worked :X :* 10x

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 10:08:07

Oh, your YEAR column does not include century? Only "90", not "1990"?
It would have been nice if you provided that piece of information.

If your year do not include century, use this

DATEADD(MONTH, 12 * theYear + theMonth, theDay - 1)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources