Get the Current Date

Question

Get the Current Date

Viewing 15 posts - 16 through 30 (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

Jeff Moden (10/27/2010)
Brandie Tarvin (10/27/2010)
Stefan Krzywicki (10/27/2010)
Interesting, I'll have to look into what the formatting numbers at the end do to the dates. Thanks
Look up Convert, the Transact-SQL reference, in Books Online. Halfway down the page, it has all the styles listed.
And you're welcome.
But, my recommendation is to avoid them like the plague.... they're comparatively slow.

Still, always nice to know how something works.

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

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

CELKO (10/27/2010)
bitbucket-25253 (10/25/2010)
In the manner of a general reply, assuming you may wish to have a "bag of date tips and all, try this link, and you can add the T-SQL to you bag of tricks.
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Nice blog post. That collects all of the Sybase/UNIX traditional tricks together. The DATE versions for some of them are:
SELECT
DATEADD(DD, DATEDIFF(DD, 0, CURRENT_TIMESTAMP), 0), --beginning of this day, traditional
CAST(CURRENT_TIMESTAMP AS DATE); -beginning of this day
DATEADD(DD, +1, CAST(CURRENT_TIMESTAMP AS DATE)); --beginning of next day;
DATEADD(DD, -1, CAST(CURRENT_TIMESTAMP AS DATE));--beginning of previous day,
GO
=========
SELECT
DATEADD(MM, DATEDIFF(MM, 0, @in_date), 0), --beginning of this month, traditional
DATEADD(DD, -DAY(@in_date)+1, CAST(@in_date AS DATE)), --beginning of this month
DATEADD (DD, -DAY(@in_date)+1, DATEADD(MM, +1, CAST(@in_date AS DATE))), --beginning of next month
DATEADD (DD, -DAY(@in_date)+1, DATEADD(MM, -1, CAST(@in_date AS DATE))); --beginning of last month
GO
=========
In ANSI the syntax would be more natural since it uses plus and minus and the ability to use year-month-day and hour-minute-second intervals.
CAST(CURRENT_TIMESTAMP AS DATE) + INTERVAL 01' DAY
CAST(CURRENT_TIMESTAMP AS DATE) - INTERVAL 01' DAY
Likewise, a mixed interval
SET expiry_date = CURRENT_TIMESTAMP + INTERVAL '20:30:15.9999' HOUR SECOND;
For periods of time like quarters, weeks, fiscal units, etc. I tend to use a report range auxiliary table.

Interesting, I thnk I might go through the entire list, work up the ANSI equivalents and see what the differences are, if any. Either way, it'll let me see which I consider more readable/clearer.

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

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 3

CELKO (10/27/2010)
[In ANSI the syntax would be more natural since it uses plus and minus and the ability to use year-month-day and hour-minute-second intervals.
.
.
.)
Likewise, a mixed interval
SET expiry_date = CURRENT_TIMESTAMP + INTERVAL '20:30:15.9999' HOUR SECOND;
For periods of time like quarters, weeks, fiscal units, etc. I tend to use a report range auxiliary table.

I realize the accuracy is a bit less but I don't find the following to be any less natural to read. Considering that it's SQL Server and that SQL Server doesn't yet have "INTERVAL", it's one of the least complicated choices and pretty much mimics the "INTERVAL".

SET expiry_date = CURRENT_TIMESTAMP + CAST('20:30:15.999' AS DATETIME);

Would I actually do it that way? Probably not... I wouldn't hardcode the time. 😉

For periods of time like quarters, weeks, fiscal units, etc. I tend to use a report range auxiliary table.

Not that my 2 cents matters, but with only rare exceptions that I can't recall right now, I absolutely agree.

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

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

Odd. When I do a "SELECT current_timestamp, GetDate()", I get the exact same values. So I'm not getting the point about readability.

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Brandie Tarvin (10/28/2010)
Odd. When I do a "SELECT current_timestamp, GetDate()", I get the exact same values. So I'm not getting the point about readability.

The point was that internally they both call the exact same functions. He was saying that it is easier to read the code for

select getdate()

instead of

SELECT current_timestamp

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

Sean,

Thanks for pointing out that he's talking about syntax, but when I go back to read his post, he's saying ANSI is more readable than T-SQL.

I don't know why I got it stuck in my head that he was talking results, not syntax. It's going to be one of *those* days. @=/

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 7

Brandie Tarvin (10/28/2010)
Sean,
Thanks for pointing out that he's talking about syntax, but when I go back to read his post, he's saying ANSI is more readable than T-SQL.
I don't know why I got it stuck in my head that he was talking results, not syntax. It's going to be one of *those* days. @=/

He is indeed, but I really think readability, when it comes to something simple like this, is more a matter of what you're used to than any objective standard.

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 8

Stefan Krzywicki (10/28/2010)
Brandie Tarvin (10/28/2010)
Sean,
Thanks for pointing out that he's talking about syntax, but when I go back to read his post, he's saying ANSI is more readable than T-SQL.
I don't know why I got it stuck in my head that he was talking results, not syntax. It's going to be one of *those* days. @=/
He is indeed, but I really think readability, when it comes to something simple like this, is more a matter of what you're used to than any objective standard.

Yes - 100% preference. When I have the option of ansi or non-ansi..I typically go with ansi.

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 9

Derrick... I have to ask... did you ever work at Grainger? I ask because worked with a Derrick Smith from Grainger Corporate Headquarters.

--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 SSC Guru Points: 1004353 More actions · Answer 10

Just in case anyone asks... when given the choice, I use what works. I have virtually no regard for ANSI code because I don't believe in the myth of truly portable code especially between SQL Server, Oracle, and MySQL.

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

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

Hey, Jeff. If you can fit it in your suitcase, it's portable. May not work in a new system, but it's portable. @=)

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.

Derrick Smith SSChampion Points: 10833 More actions · Answer 12

Jeff Moden (10/28/2010)
Derrick... I have to ask... did you ever work at Grainger? I ask because worked with a Derrick Smith from Grainger Corporate Headquarters.

Nope. Someone asked me the same thing a month or two ago too..I'll have to have a word with him and see if he can change his name.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Brandie Tarvin (10/28/2010)
Sean,
Thanks for pointing out that he's talking about syntax, but when I go back to read his post, he's saying ANSI is more readable than T-SQL.
I don't know why I got it stuck in my head that he was talking results, not syntax. It's going to be one of *those* days. @=/

He's talking about ANSI SQL (i.e. the standard that's approved for expressing SQL as described by ANSI) as opposed to T-SQL's "custom" implementation.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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

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

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

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