Get the Current Date

  • WayneS (11/17/2010)


    I think SSIS is using .Net functions. It would support current_timestamp in an ExecuteSQL task.

    It does, but if you're using expressions, say to create a file name with an appended date, you have to use GetDate().

    For example

    (Year(GetDate) * 10000) + (Month(GetDate()) * 100) + Day(GetDate()) for today gives you

    20101117

    Then you cast that as a string when appending it to the end of your filename.

    Using (Year(Current_Timestamp) * 10000) + (Month(Current_Timestamp) * 100) + Day(Current_Timestamp) results in errors as the Expression validator doesn't recognize the "variable" Current_Timestamp.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • As I've never used Current_Timestamp, I did not know it was unsupported in SSIS expressions. Thanks for the update, Stefan.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (11/17/2010)


    As I've never used Current_Timestamp, I did not know it was unsupported in SSIS expressions. Thanks for the update, Stefan.

    I ran across that today and thought I should mention it somewhere.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • Stefan Krzywicki (11/17/2010)


    Well, I've found one reason to use GetDate() instead of Current_Timestamp. Current_Timestamp isn't supported in SSIS Expressions.

    I'm fairly certain that there is no ANSI standard for SSIS. 🙂

  • There is a rather nasty effect of using CURRENT_TIMESTAMP:

    Original DDL statements:

    create table dbo.foo

    ( MyTimeStamp date )

    go

    alter table dbo.foo add default ( CURRENT_TIMESTAMP ) FOR MyTimeStamp

    go

    Then if you script, select or use a GUI to view the default, you will see:

    CREATE TABLE [dbo].[foo](

    [MyTimeStamp] [date] NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[foo] ADD DEFAULT (getdate()) FOR [MyTimeStamp]

    GO

    Note that SQL Server has changed CURRENT_TIMESTAMP to getdate()

    SQL = Scarcely Qualifies as a Language

  • Carl Federl (11/17/2010)


    There is a rather nasty effect of using CURRENT_TIMESTAMP:

    Original DDL statements:

    create table dbo.foo

    ( MyTimeStamp date )

    go

    alter table dbo.foo add default ( CURRENT_TIMESTAMP ) FOR MyTimeStamp

    go

    Then if you script, select or use a GUI to view the default, you will see:

    CREATE TABLE [dbo].[foo](

    [MyTimeStamp] [date] NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[foo] ADD DEFAULT (getdate()) FOR [MyTimeStamp]

    GO

    Note that SQL Server has changed CURRENT_TIMESTAMP to getdate()

    HA! That's funny. Shows what Microsoft thinks of ANSI standards!

    Not sure why you say it is "nasty" though.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • Well, BOL does say (for CURRENT_TIMESTAMP)

    This function is the ANSI SQL equivalent to GETDATE.

    I guess they really mean it's an alias for it.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 7 posts - 31 through 36 (of 36 total)

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