Get the Current Date

Question

Post reply

Get the Current Date

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

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

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

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

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 2

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.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

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

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 6

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

WayneS SSC Guru Points: 95460 More actions · Answer 7

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