SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT


CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT

Author
Message
Possinator
Possinator
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 1123
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. :-D

Thanks!

Looking for a Deadlock Victim Support Group..
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90417 Visits: 41147
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6030 Visits: 11771
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.c
dwain.c
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7663 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10876 Visits: 11970
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search