CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT

Question

Post reply

CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT

LoosinMaMind

Hall of Fame

Points: 3366
More actions
September 12, 2012 at 1:00 pm

#266559

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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 SSC Guru Points: 442467 More actions · Answer 3

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

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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

Possinator Hall of Fame Points: 3422 More actions · Answer 6

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.000112012

2012-03-02 00:00:00.000122012

2012-04-02 00:00:00.000132012

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

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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 SSC Guru Points: 442467 More actions · Answer 8

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.000112012
2012-03-02 00:00:00.000122012
2012-04-02 00:00:00.000132012
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.

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 9

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

Possinator Hall of Fame Points: 3422 More actions · Answer 10

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

I'm just too slow. Should have checked the post again. 'twas edited whilst I typed. 😀

Thanks!

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

LoosinMaMind (9/12/2012)
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

If you wanted to make it so you could query the table directly instead of having code do it over and over again, add a persisted computed column to the table to do the calculation that Lowel posted. Just pray people named their columns for inserts or BOOM!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

I think this code is about as short as it gets to do this:

select

[DD-MM-YYYY] =

convert(varchar(10),dateadd(mm,(12*YEAR_)-22801+MONTH_,DAY_-1),105),

[Date]=dateadd(mm,(12*YEAR_)-22801+MONTH_,DAY_-1),

a.*

from

PS_TestForOnline a

Results:

DD-MM-YYYY Date DAY_ MONTH_ YEAR_

---------- ----------------------- ----------- ----------- -----------

01-01-2012 2012-01-01 00:00:00.000 1 1 2012

01-02-2012 2012-02-01 00:00:00.000 1 2 2012

01-03-2012 2012-03-01 00:00:00.000 1 3 2012

(3 row(s) affected)

More info on this subject in this link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

This is pretty short too but subject to the DATEFORMAT setting (I think):

SELECT DAY_, MONTH_, YEAR_

,CAST(RTRIM(MONTH_) + '-' + RTRIM(DAY_) + '-' + RTRIM(YEAR_) AS DATETIME)

FROM PS_TestForOnline

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sergiy SSC Guru Points: 110208 More actions · Answer 14

Jeff Moden (9/12/2012)Just pray people named their columns for inserts or BOOM!

Not necessarily.

If you add a computed column at the end of the table it would not break existing inserts, even if the columns are not listed.

This works for me:

CREATE TABLE test (

ID int,

NAME nvarchar(50),

DESCR AS (CONVERT(nvarchar(20), ID) + ' - ' + NAME )

)

INSERT INTO test

SELECT 1, 'Number One'

SELECT * FROM Test

_____________
Code for TallyGenerator