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:42 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 5:00 PM
Points: 494, Visits: 1,121
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!


Looking for a Deadlock Victim Support Group..
Post #1358194
Posted Wednesday, September 12, 2012 3:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:21 PM
Points: 35,951, Visits: 30,235
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1358246
Posted Wednesday, September 12, 2012 8:21 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 3,081, Visits: 11,230
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



Post #1358297
Posted Wednesday, September 12, 2012 11:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 3,590, Visits: 5,096
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!
Post #1358339
Posted Thursday, September 13, 2012 7:42 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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


Post #1359016
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse