Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 1:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:20 AM
Points: 148, Visits: 453
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 12,744, Visits: 31,081
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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






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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
Lowell is right, though as you should use the DATE data type, not a character string, especially if you are comparing or storing dates.



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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:20 AM
Points: 148, Visits: 453
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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




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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:00 PM
Points: 494, Visits: 1,121
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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/



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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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.



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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:20 AM
Points: 148, Visits: 453
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 )

Thanks again
Post #1358187
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse