CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT

  • 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

  • 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!

  • 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

  • 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 !!!

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

  • 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

  • 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]
  • 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/

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

  • 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

  • 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]
  • 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)

  • 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

  • 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

  • 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

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

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