October 25, 2010 at 10:43 am
I'm using GetDate() to get the current date in some insert queries. Most of what I've seen says there's no difference between methods of obtaining the date. Is that right or are there some hidden pitfalls I should be aware of?
Currently using
SELECT CONVERT(DATE, GETDATE())
Anything better than that? Should I be using
SELECT CAST(GETDATE() as Date)
--------------------------------------
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
October 25, 2010 at 12:57 pm
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
October 25, 2010 at 1:13 pm
CELKO (10/25/2010)
Actually, you need to start using the ANSI/ISO Standard CURRENT_TIMESTAMP and not the old Sybase/UNIX getdate() now that we have it in SQL Server.
Why do you say "Need"? Is there any documentation that GETDATE() will be depreciated? Or is it just preference. Is there any performance benefit? I haven't seen one, but I haven't looked as exhaustively as others might have.
CONVERT () is also proprietary, but it has other problems. Display is supposed to be done in the front end in a tiered architecture and not in the database; this is a hold-over from the old Sybase days of monolithic coding. Two machines with different language setting can mess you up, so you kludge it with lots of calls to CONVERT(). Ugh!
Proprietary doesn't concern me. I'm using SQL Server so I might as well get full use of its abilities.
I'm not using it for display, as you say that is the purview of the front end, I'm just using to convert it to DATE since I'm using a field type of DATE, not DATETIME. Normally I'd use CAST(GetDate() as Date). I went with CONVERT(DATE, GETDATE()) because that's how it was done on Books Online when I was looking to see if Microsoft had stated a preference between GetDate(), CURRENT_TIMESTAMP or some other method. I figured I'd give it a try and look around to see if there was any reason to use one or the other. I haven't seen one as yet.
ANSI/ISO Standard SQL allows only one format for temporal display and input, namely the ISO-8601 'YYYY-MM-DD HH:MM:SS.SSSS.." one. You probably already noticed that the new DATE data type defaults to this. You can expect to see SQL Server getting more and more standard, so start doing it correctly now and save a lot of work for the next guy on the system.
I've noticed the DATE type displays as 'YYYY-MM-DD' in queries, but I've also noticed it accepts 'MM/DD/YYYY' for input. Is there any documentation that SQL Server will be limiting input to the same format as display? I keep asking about documentation because I like to read the originals, it often provides a jumping point for further reading.
This is why I would use "CAST (CURRENT_TIMESTAMP AS DATE)" until we get CURRENT_DATE in SQL Server.
Thanks for the input, I appreciate it.
--------------------------------------
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
October 25, 2010 at 1:14 pm
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
Thanks, always nice to have examples all in one place.
--------------------------------------
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
October 25, 2010 at 1:15 pm
Just for the record, getdate() and current_timestamp perform the exact same operation on the back end, so there is no data/performance difference at all between them. The only difference is current_timestamp is the ANSI standard and may be more readable. Getdate() is not deprecated.
October 25, 2010 at 1:19 pm
Derrick Smith (10/25/2010)
Just for the record, getdate() and current_timestamp perform the exact same operation on the back end, so there is no data/performance difference at all between them. The only difference is current_timestamp is the ANSI standard and may be more readable. Getdate() is not deprecated.
Ah! Excellent, that's exactly the kind of information I was looking for. How did you find this out? I'd love to look at either the documentation so I have an idea of where to look next time something like this comes up or even better, is there a way to find this out through testing?
I'd been wondering if there was a performance difference or execution difference since GetDate() looks like it is a function while CURRENT_TIMESTAMP looks like a referenced variable.
Thanks!
--------------------------------------
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
October 25, 2010 at 2:36 pm
While it's possible GetDate() will be removed at some point, there's nothing indicating it will be at this time.
I prefer it, because:
select getdate()
looks like it's selecting a function, while:
select current_timestamp
looks like it's selecting a column. That's just how my eyes are trained, and it makes it easier for me to sight-read the code. Pick the one you like. Either Joe's practical "It's the standard, so use it", or my "It makes it easier to read". Your call on that one, so far as I can tell.
Of course, color-coding makes either easy to spot, but I don't always have that available.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2010 at 3:17 pm
If you are use the new DATETIME2 data types, you can use SYSDATETIME or SYSUTCDATETIME to get a more precise value. There is no real penalty for using DATETIME2, because it uses no more storage than DATETIME.
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
/* Returns
SYSDATETIME() 2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP 2007-04-30 13:10:02.047
GETDATE() 2007-04-30 13:10:02.047
GETUTCDATE() 2007-04-30 20:10:02.047
/*
This also works:
SELECT CONVERT(DATE, SYSDATETIME())
October 25, 2010 at 3:30 pm
Michael Valentine Jones (10/25/2010)
If you are use the new DATETIME2 data types, you can use SYSDATETIME or SYSUTCDATETIME to get a more precise value. There is no real penalty for using DATETIME2, because it uses no more storage than DATETIME.
SELECT SYSDATETIME(),SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
/* Returns
SYSDATETIME() 2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP 2007-04-30 13:10:02.047
GETDATE() 2007-04-30 13:10:02.047
GETUTCDATE() 2007-04-30 20:10:02.047
/*
This also works:
SELECT CONVERT(DATE, SYSDATETIME())
I'm actually going for a less precise value : -) All I need is date.
--------------------------------------
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
October 27, 2010 at 5:34 am
I prefer Convert() over Cast() because I can do string tricks with my GETDATE() and still take it back to a valid datetime value. I'm not sure if you can use the formating Convert() offers with the Cast() function.
Run the below, for example:
SELECT Convert(varChar(23),GetDate(),101);
SELECT Convert(varChar(23),GetDate(),110);
SELECT Convert(varChar(23),GetDate(),120);
SELECT Convert(varChar(23),GetDate(),121);
SELECT Convert(varChar(23),GetDate(),114);
After I do my Substring() and other expressional tricks, I can nest the whole thing into a Convert(Datetime,(myotherconvert&stringtricks),XXX) to get it back to a datetime. With XXX being the reformat.
Or you can use it straight from datetime to datetime to convert the date into a different collation format. Like US date styles to European date styles and vice-versa.
Does that help?
October 27, 2010 at 6:12 am
Cast() doesn't do styles. You're right about that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2010 at 7:04 am
Brandie Tarvin (10/27/2010)
I prefer Convert() over Cast() because I can do string tricks with my GETDATE() and still take it back to a valid datetime value. I'm not sure if you can use the formating Convert() offers with the Cast() function.Run the below, for example:
SELECT Convert(varChar(23),GetDate(),101);
SELECT Convert(varChar(23),GetDate(),110);
SELECT Convert(varChar(23),GetDate(),120);
SELECT Convert(varChar(23),GetDate(),121);
SELECT Convert(varChar(23),GetDate(),114);
After I do my Substring() and other expressional tricks, I can nest the whole thing into a Convert(Datetime,(myotherconvert&stringtricks),XXX) to get it back to a datetime. With XXX being the reformat.
Or you can use it straight from datetime to datetime to convert the date into a different collation format. Like US date styles to European date styles and vice-versa.
Does that help?
Interesting, I'll have to look into what the formatting numbers at the end do to the dates. Thanks
--------------------------------------
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
October 27, 2010 at 7:28 am
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.
October 27, 2010 at 11:17 am
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. ThanksLook 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2010 at 11:23 am
Jeff Moden (10/27/2010)
But, my recommendation is to avoid them like the plague.... they're comparatively slow.
How much of a performance hit do styles put on the server?
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply