CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » CONCATINATE 3 COLUMNS (int) INTO A WORKABLE...

Add to briefcase

15 posts, Page 1 of 2

1 2 »»

CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT

Expand / Collapse

Author

Message

Posted Wednesday, September 12, 2012 1:00 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:36 AM
Points: 140, Visits: 424

Hi

I have been given a new system to work on and the dates are held in three seperate columns

day number ;ie 1,2,3,4 etc
month number ; ie 9,10,11 etc
year; 2011, 2012 etc

The format of these is an INT

I need to somehow concatinate the 3 into a workable date format in a SELECT statement

Any help would be greatly appreciated.

Thanks in advance

SAMPLE DATA AND EXPECTED RESULTS SCRIPT BELOW.

CREATE TABLE PS_TestForOnline
(
DAY_ INT,
MONTH_ INT,
YEAR_ INT
);

INSERT INTO PS_TestForOnline
VALUES(1,1,2012);
INSERT INTO PS_TestForOnline
VALUES(1,2,2012);
INSERT INTO PS_TestForOnline
VALUES(1,3,2012);

SELECT * FROM PS_TestForOnline

--&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

--EXPECTED RESULTS

SELECT

'1-1-2012' DATE_EXAMPLE_1, '1-2-2012' DATE_EXAMPLE_2, '1-3-2012' DATE_EXAMPLE_3 -- NEED THESE TO BE IN DATE FORMAT DD-MM-YYYY

--&&&&&&&&&&&&&&&&&&&&&&

DROP TABLE PS_TestForOnline

Post #1358171

Posted Wednesday, September 12, 2012 1:08 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:50 PM
Points: 11,645, Visits: 27,735

personally, since you have integers, i'd stick with using the dATEADD() functions:

/*
2012-02-01 00:00:00.000
2012-03-01 00:00:00.000
2012-04-01 00:00:00.000
*/
CREATE TABLE PS_TestForOnline
(
 DAY_ INT,
 MONTH_ INT,
 YEAR_ INT
);

INSERT INTO PS_TestForOnline
VALUES(1,1,2012);
INSERT INTO PS_TestForOnline
VALUES(1,2,2012);
INSERT INTO PS_TestForOnline
VALUES(1,3,2012);


SELECT DATEADD(dd,DAY_ -1,
               DATEADD(mm,MONTH_ -1,
               DATEADD(yy,(YEAR_ - 1900) ,0))),
* FROM PS_TestForOnline

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1358173

Posted Wednesday, September 12, 2012 1:12 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463

Easy.


CREATE TABLE PS_TestForOnline
(
 DAYNBR INT,
 MONTHNBR INT,
 YEARNBR INT
);

INSERT INTO PS_TestForOnline
VALUES(1,1,2012);
INSERT INTO PS_TestForOnline
VALUES(1,2,2012);
INSERT INTO PS_TestForOnline
VALUES(1,3,2012);


SELECT *, right('0' + cast(DAYNBR as varchar), 2) + '-' + right('0' + cast(MONTHNBR as varchar), 2) + '-' + cast(YEARNBR as varchar) FROM PS_TestForOnline

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1358177

Posted Wednesday, September 12, 2012 1:14 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463

Lowell is right, though as you should use the DATE data type, not a character string, especially if you are comparing or storing dates.

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1358179

Posted Wednesday, September 12, 2012 1:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:36 AM
Points: 140, Visits: 424

Cool !!!

That the sort of thing i need as it going into a Business Intelligence system where users have to query aginst dates.

However, your dates come out as the 2nd of each month, ????

i would expect my results to be ......

INSERT INTO PS_TestForOnline
VALUES(1,1,2012); 2012-01-01 00:00:00.000
INSERT INTO PS_TestForOnline
VALUES(1,2,2012); 2012-02-01 00:00:00.000
INSERT INTO PS_TestForOnline
VALUES(1,3,2012); 2012-03-01 00:00:00.000

Thanks in advance

PS: just out of interest, why the -1900??

Post #1358181

Posted Wednesday, September 12, 2012 1:20 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463

LoosinMaMind (9/12/2012)

Cool !!!

That the sort of thing i need as it going into a Business Intelligence system where users have to query aginst dates.

However, your dates come out as the 2nd of each month, ????

i would expect my results to be ......

INSERT INTO PS_TestForOnline
VALUES(1,1,2012); 2012-01-01 00:00:00.000
INSERT INTO PS_TestForOnline
VALUES(1,2,2012); 2012-02-01 00:00:00.000
INSERT INTO PS_TestForOnline
VALUES(1,3,2012); 2012-03-01 00:00:00.000

Thanks in advance

PS: just out of interest, why the -1900??

Not sure what you are talking about with regard to the 2nd of each month, both solutions return the first of each month.

As for the - 1900, run this:

select dateadd(yy, 2012 - 1900, 0) -- should return 2012-01-01 00:00:00.000

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1358183

Posted Wednesday, September 12, 2012 1:21 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, April 19, 2013 2:23 PM
Points: 494, Visits: 1,110

Lowell (9/12/2012)

personally, since you have integers, i'd stick with using the dATEADD() functions:

/*
2012-02-01 00:00:00.000
2012-03-01 00:00:00.000
2012-04-01 00:00:00.000
*/
CREATE TABLE PS_TestForOnline
(
 DAY_ INT,
 MONTH_ INT,
 YEAR_ INT
);

INSERT INTO PS_TestForOnline
VALUES(1,1,2012);
INSERT INTO PS_TestForOnline
VALUES(1,2,2012);
INSERT INTO PS_TestForOnline
VALUES(1,3,2012);


SELECT DATEADD(dd,DAY_ -1,
               DATEADD(mm,MONTH_ -1,
               DATEADD(yy,(YEAR_ - 1900) ,0))),
* FROM PS_TestForOnline

Hi Lowell - yours is adding 1 to the month and day since the year is already 01-01:

(No column name)	DAY_	MONTH_	YEAR_
2012-02-02 00:00:00.000	1	1	2012
2012-03-02 00:00:00.000	1	2	2012
2012-04-02 00:00:00.000	1	3	2012

You could subtract the extract day:

SELECT DATEADD(dd,DAY_ - 1, DATEADD(mm,MONTH_ - 1, DATEADD(yy,(YEAR_ - 1900) ,0)))
FROM #PS_TestForOnline

or use a string concat:

select cast(convert (char(4), p.YEAR_) 
    + '-' + convert(char(2), p.MONTH_) + '-' 
    + convert(char(2),p.DAY_) as date)
from PS_TestForOnline p

Looking for a Deadlock Victim Support Group..

Post #1358184

Posted Wednesday, September 12, 2012 1:24 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463

A little more information. The date '1900-01-01 00:00:00.000' is sometimes referred to as the 0 (zero) date. If you run select cast(0 as datetime) you will return '1900-01-01'. Knowing this helps wih completing a variety date calculations. To see some, go here:

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1358185

Posted Wednesday, September 12, 2012 1:26 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463

Possinator (9/12/2012)

Lowell (9/12/2012)

personally, since you have integers, i'd stick with using the dATEADD() functions:

/*
2012-02-01 00:00:00.000
2012-03-01 00:00:00.000
2012-04-01 00:00:00.000
*/
CREATE TABLE PS_TestForOnline
(
 DAY_ INT,
 MONTH_ INT,
 YEAR_ INT
);

INSERT INTO PS_TestForOnline
VALUES(1,1,2012);
INSERT INTO PS_TestForOnline
VALUES(1,2,2012);
INSERT INTO PS_TestForOnline
VALUES(1,3,2012);


SELECT DATEADD(dd,DAY_ -1,
               DATEADD(mm,MONTH_ -1,
               DATEADD(yy,(YEAR_ - 1900) ,0))),
* FROM PS_TestForOnline

Hi Lowell - yours is adding 1 to the month and day since the year is already 01-01:

(No column name)	DAY_	MONTH_	YEAR_
2012-02-02 00:00:00.000	1	1	2012
2012-03-02 00:00:00.000	1	2	2012
2012-04-02 00:00:00.000	1	3	2012

You could subtract the extract day:

SELECT DATEADD(dd,DAY_ - 1, DATEADD(mm,MONTH_ - 1, DATEADD(yy,(YEAR_ - 1900) ,0)))
FROM #PS_TestForOnline

or use a string concat:

select cast(convert (char(4), p.YEAR_) + '-' + convert(char(2), p.MONTH_) + '-' + convert(char(2),p.DAY_) as date)
from PS_TestForOnline p

Actually, it isn't. Take a closer look, it is actually subtracting 1 for the day or month entered in the table.

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1358186

Posted Wednesday, September 12, 2012 1:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:36 AM
Points: 140, Visits: 424

Thanks Chaps, thats done the trick.

Love this site so much.

As long as you supply sample data and expected reults. (iI've learnt in the past Smile

Smile

)

Thanks again

Post #1358187

« Prev Topic | Next Topic »

Add to briefcase

15 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse