what will be the value of YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()))

  • hi i am

    writing an expression for Dervied column in SSIS

    ie

    YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE())

    to get the Value of Current Date in YYYMMDD format

    my Derived column is in the Integer format ,,

    so will this expression will be enough to get the current date in YYYMMDD format ..

    or this exprression will give me the sum of (YEAR+MON+DAY) as my derived column type is Int ...

    Please help,

  • You can definately test that in ur query window.

    YEAR(), MONTH() and DAY() functions return integer, so if u're adding them u'll definately get a sum of these three integers.

    I'm not sure what u mean by YYY. Either we get YY or YYYY

    to get a date in YYYYMMDD format you can convert the date with 112 format.

    select convert(varchar, getdate(), 112) for YYYYMMDD

    OR

    select convert(varchar, getdate(), 12) for YYMMDD



    Pradeep Singh

  • deepak.spiral (12/23/2008)


    my Derived column is in the Integer format ,,

    so will this expression will be enough to get the current date in YYYMMDD format ..

    or this exprression will give me the sum of (YEAR+MON+DAY) as my derived column type is Int ...

    If your target column is INT, U can write something like this...

    declare @a int

    set @a=cast(convert(varchar, getdate(), 112) as int) -- Explicity converting the result to int

    print @a

    declare @a int

    set @a=convert(varchar, getdate(), 112) as int -- Implicit conversion to INT...

    print @a



    Pradeep Singh

  • thanks a lot for ur answers

    this Select query Statement doest work within Derived column Expression,

    so i have used same query to get the date in the same(YYYYMMDD) format and stored into a variable and using the same..for my Derived cloumns Exprression.

    Thanks Again

  • Just so you know... storing dates in a database like that is a form of "Death by SQL"... it will bite you sometime in the future.

    The only reason you should ever format dates in SQL Server is if the output is going to a file that requires it.

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

  • Jeff Moden (12/29/2008)


    Just so you know... storing dates in a database like that is a form of "Death by SQL"... it will bite you sometime in the future.

    The only reason you should ever format dates in SQL Server is if the output is going to a file that requires it.

    :hehe::hehe::hehe: Jeff, interesting description "Death by SQL" - it can be occur sometimes 😛

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Hmmm.. there was a silly copy/paste error in the following line :w00t:..

    set @a=convert(varchar, getdate(), 112) as int -- Implicit conversion to INT...

    You need to remove the part mentioned in bold from the above line.....



    Pradeep Singh

  • hi ALLL

  • hi ALLL i have a colomn keeping all the dates in the format YYYYDDMM .

    my Requiremnt is to calculate the differnce of days from (YYYYMMDD) INT datatype column with the Current Date.

    current date can be availabe iin the getdate() form ...

    so issue is to calculate days differece (getdate()-YYYYMMDD)

    so which will be the right way ..

    Please help ,,,

    i guess it will be better if calculate the difference in the YYYY-MM-DD HH:MM ,,because YYYYMMDD-YYYYMMDD is not giving Proper Days Difference

  • Jeff Moden (12/29/2008)


    Just so you know... storing dates in a database like that is a form of "Death by SQL"... it will bite you sometime in the future.

    The only reason you should ever format dates in SQL Server is if the output is going to a file that requires it.

    Deepak - Mark Jeff's word... U landed into trouble so soon...

    Had you kept the dates in the native Sql server format, you just needed to use the DATEDIFF() function.....

    If you're planning to segregate year, month and days from YYYYMMDD format, you ought to consider leap years as well 😛

    I suggest you keep the date in a DATETIME feild rather than converting it into INT...



    Pradeep Singh

  • It is definitely a huge mistake to store dates as integers. This is just one of the many issues you are going to run into - but, if you cannot change the column to a datetime (or date in SQL Server 2008) then I suggest you create a view or a computed column that casts the integer date to a valid datetime.

    CREATE VIEW dbo.MyView AS

    SELECT ...

    ,CAST(CAST(datecolumn AS char(8)) AS datetime) AS MyDateColumn

    FROM dbo.MyTable

    GO

    You could also create a computed column on the table instead of using a view.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hmmm... Simple addition will NOT work. Multiplication is also needed.

    Example 1: 2009 + 1 + 2 = 2012 (January 2, 2009)

    Example 2: 2009 + 2 + 1 = 2012 (February 1, 2009)

    Corrected: (2009 * 10000) + (1 * 100) + 2 = 20090102

    Having noted that, dates should be stored AS dates unless there is a completely compelling reason. Even then, the best course is probably a computed column for the desired component(s). Whether the computed column should be actualized (physically stored in the database) depends on other factors, such as the need for indexing to facilitate processing.

  • PhilPacha (12/29/2008)


    Hmmm... Simple addition will NOT work. Multiplication is also needed.

    Example 1: 2009 + 1 + 2 = 2012 (January 2, 2009)

    Example 2: 2009 + 2 + 1 = 2012 (February 1, 2009)

    Corrected: (2009 * 10000) + (1 * 100) + 2 = 20090102

    Having noted that, dates should be stored AS dates unless there is a completely compelling reason. Even then, the best course is probably a computed column for the desired component(s). Whether the computed column should be actualized (physically stored in the database) depends on other factors, such as the need for indexing to facilitate processing.

    All of that isn't necessary nor will it account for certain things like leap years, etc. The best way to do it is like Jeffrey Williams did... convert it to CHAR(8) and convert that to a real datetime which will account for leap years and all other manner of date based computational caveats. 😛

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

  • "Death by SQL'" ... hmmm ... does that make M$ both 'dangerous' and 'suicidal' for doing it in the msdb ???

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Oh grasshopper, you're digging a deep hole. All that is required as was stated earlier is DATEDIFF() using GETDATE() as one of your arguments, which will give you the difference between two datetimes in Years, Months, Days, Minutes, seconds, etc. as an integer. No need to go through all this conversion stuff.

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

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