A common question – I often get from Oracle/MySQL Professionals:
“What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?”
Here is a common question I often get from SQL Server Professionals:
“What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?”
Very simple question but have showed up so frequently that I feel like to write about it.
Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine.
You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server (Reference Link). CURRENT_TIMESTAMP is an ANSI SQL function, whereas GETDATE is T-SQL implementation of the same function. Both of them derive value from the operating system of the computer on which SQL Server instance is running.
Above discussion prompts another question – in this case, what should one use GETDATE or CURRENT_TIMESTAMP?
Well, this is indeed tricky and interesting question. I think I am very comfortable using the GETDATE () so I will go to use it but a matter of the fact there is no right or wrong answer. If you want to follow ancient saying “When in Rome, do as the Romans do”, I suggest using the GETDATE (), or continue using CURRENT_TIMESTAMP.
With that said, there is one very important property we all need to keep in mind. If you use CURRENT_TIMESTAMP while creating an object, they are automatically converted to GETDATE() and stored internally. To illustrate what I am suggesting here is the example –
Create a table using the following script
CREATE TABLE [dbo].[TestTable]( [Cold2] [datetime] NULL
) ON [PRIMARY]
GO ALTER TABLE [dbo].[TestTable] ADD DEFAULT (CURRENT_TIMESTAMP) FOR [Cold2]
Now go to SSMS and generate the script for the table and you will notice following syntax.
CREATE TABLE [dbo].[TestTable]( [Cold2] [datetime] NULL
) ON [PRIMARY]
GO ALTER TABLE [dbo].[TestTable] ADD DEFAULT (GETDATE()) FOR [Cold2]
You can notice that SQL Server have automatically converted CURRENT_TIMESTAMP to GETDATE(). I guess this gives us an idea how they behave. Now go ahead and make your choice! Do let me know which one will you use CURRENT_TIMESTAMP or GETDATE () in the comments area.
I quite often receive questions where users are looking for solution to following error:
Microsoft OLE DB Provider for SQL Server error ‘80040e07′ Syntax error converting datetime from character string.
Microsoft SQL Native Client error ‘80040e07′ Syntax error converting datetime from character string.
If you have ever faced above error – I have a very simple solution for you.
The solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error.
INSERT INTO TestTable (ID, MyDate)
VALUES (1, '01-Septeber-2013')
The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors.
The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error.
Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.
Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted.
You can use the choose function on table as well. Here is the quick example of the same.
GO SELECT A.ModifiedDate, DATENAME(dw, A.ModifiedDate) DayofWeek, CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday', 'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay FROM [Person].[Address] A
If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function.
Though I have written more than 2300 blog posts, I always find things which I have not covered earlier in this blog post. Recently I was asked if I have written a function which rounds up or down the time based on the minute interval passed to it. Well, not earlier but it is here today.
Here is a very simple example of how one can do the same.
CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME, @RoundToMin INT) RETURNS DATETIME
RETURN ROUND(CAST(CAST(CONVERT(VARCHAR,@Time,121) AS DATETIME) AS FLOAT) * (1440/@RoundToMin),0)/(1440/@RoundToMin) END GO
Above function needs two values. 1) The time which needs to be rounded up or down. 2) Time in minutes (the value passed here should be between 0 and 60 – if the value is incorrect the results will be incorrect.) Above function can be enhanced by adding functionalities like a) Validation of the parameters passed b) Accepting values like Quarter Hour, Half Hour etc.
I have been asked this question a number of times and my answer always has been “Search online and you will find the answer.” Every single time someone follows my answer, he finds the accurate answer in just a few clicks. However, this question is getting very popular nowadays, so I decided to answer this question through a blog post.
I usually prefer creating my own T-SQL script but in today’s case, I have taken the script from the Web. I have seen this script in so many places that I do not know who the original creator is, so I’m not sure who should get credit for the script.
Question: How do I retrieve SQL Server Installation date?
Answer: Run the following query and it will give you the date of SQL Server Installation.
SELECT create_date FROM sys.server_principals WHERE sid= 0x010100000000000512000000
Question: I have installed SQL Server Evaluation version. How do I know what is the expiry date for it?
Answer: SQL Server evaluation period lasts for 180 days. The expiration date is always 180 days from the initial installation. The following query will give the expiration date of evaluation version:
-- Evaluation Version Expire Date SELECT create_date AS InstallationDate, DATEADD(DD, 180, create_date) AS 'Expiry Date' FROM sys.server_principals WHERE sid= 0x010100000000000512000000
I believe there is a way to do this using registry, but I have not explored it personally. Now as what I’ve said earlier, there are many different blog posts on this subject. Let me list a few which I really enjoyed to read as they shared a few more insights about this subject:
Some kinds of confusion never go away. Here is one of the ancient confusing things in SQL. The precision of the SmallDateTime is one concept that confuses a lot of people, proven by the many messages I receive everyday relating to this subject.
Let me start with the question: What is the precision of the SMALLDATETIME datatypes?
What is your answer? Write it down on your notepad.
Since the increase of social media conversations, I noticed that the amount of the comments I receive on this blog is a bit staggering. I receive lots of questions on facebook, twitter or Google+. One of the very interesting questions yesterday was asked on Facebook by Raghavendra. I am re-organizing his script and asking all of the questions he has asked me. Let us see if we could help him with his question:
CREATE TABLE #temp (name VARCHAR(100),registered smalldatetime) GO DECLARE @test smalldatetime SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT * FROM #temp ORDER BY registered DESC GO DROP TABLE #temp GO
Now when the above script is ran, we will get the following result:
Well, the expectation of the query was to have the following result. The row which was inserted last was expected to return as first row in result set as the ORDER BY descending.
Side note: Because the requirement is to get the latest data, we can’t use any column other than smalldatetime column in order by. If we use name column in the order by, we will get an incorrect result as it can be any name.
My Initial Reaction
My initial reaction was as follows:
1) DataType DateTime2: If file precision of the column is expected from the column which store date and time, it should not be smalldatetime. The precision of the column smalldatetime is One Minute (Read Here) for finer precision use DateTime or DateTime2 data type.
Here is the code which includes above suggestion:
CREATE TABLE #temp (name VARCHAR(100), registered datetime2) GO DECLARE @test datetime2 SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT * FROM #temp ORDER BY registered DESC GO DROP TABLE #temp GO
2) Tie Breaker Identity: There are always possibilities that two rows were inserted at the same time. In that case, you may need a tie breaker. If you have an increasing identity column, you can use that as a tie breaker as well.
CREATE TABLE #temp (ID INT IDENTITY(1,1), name VARCHAR(100),registered datetime2) GO DECLARE @test datetime2 SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT * FROM #temp ORDER BY ID DESC GO DROP TABLE #temp GO
Those two were the quick suggestions I provided. It is not necessary that you should use both advices. It is possible that one can use only DATETIME datatype or Identity column can have datatype of BIGINT or have another tie breaker.
CREATE TABLE #temp (name VARCHAR(100),registered smalldatetime) GO DECLARE @test smalldatetime SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number" FROM #temp ORDER BY 3 DESC GO DROP TABLE #temp GO
However, I believe it is not the solution and can be further misleading if used in a production server. Here is the example of why it is not a good solution:
CREATE TABLE #temp (name VARCHAR(100) NOT NULL,registered smalldatetime) GO DECLARE @test smalldatetime SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO -- Before Index SELECT name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number" FROM #temp ORDER BY 3 DESC GO -- Create Index ALTER TABLE #temp ADD CONSTRAINT [PK_#temp] PRIMARY KEY CLUSTERED (name DESC) GO -- After Index SELECT name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number" FROM #temp ORDER BY 3 DESC GO DROP TABLE #temp GO
Now let us examine the resultset. You will notice that an index which is created on the base table which is (indeed) schema change the table but can affect the resultset. As you can see, an index can change the resultset, so this method is not yet perfect to get the latest inserted resultset.
No Schema Change Requirement
After giving these two suggestions, I was waiting for the feedback of the asker. However, the requirement of the asker was there can’t be any schema change because the application was used by many other applications. I validated again, and of course, the requirement is no schema change at all. No addition of the column of change of datatypes of any other columns. There is no further help as well.
This is indeed an interesting question. I personally can’t think of any solution which I could provide him given the requirement of no schema change. Can you think of any other solution to this?
Need of Database Designer
This question once again brings up another ancient question: “Do we need a database designer?” I often come across databases which are facing major performance problems or have redundant data. Normalization is often ignored when a database is built fast under a very tight deadline. Often I come across a database which has table with unnecessary columns and performance problems. While working as Developer Lead in my earlier jobs, I have seen developers adding columns to tables without anybody’s consent and retrieving them as SELECT *. There is a lot to discuss on this subject in detail, but for now, let’s discuss the question first. Do you have any suggestions for the above question?
Note: I have used SQL Server 2012 for this small fun experiment.
Here is what we are going to do. We will run the script one at time instead of running them all together and try to guess the answer. I am confident that many will get it correct but if you do not get correct, you learn something new.
Let us create database and sample table.
CREATE DATABASE DB2012
GO USE DB2012
GO CREATE TABLE TableDT (DT1 VARCHAR(100), DT2 DATETIME2, DT1C AS DT1, DT2C AS DT2); INSERT INTO TableDT (DT1, DT2) SELECT GETDATE(), GETDATE() GO
There are four columns in the table. The first column DT1 is regular VARCHAR and second DT2 is DATETIME2. Both of the column are been populated with the same data as I have used the function GETDATE(). Now let us do the SELECT statement and get the result from both the columns.
Before running the query please guess the answer and write it down on the paper or notepad.
Question 1: Guess the resultset
SELECT DT1, DT2 FROM TableDT
Now once again run the select statement on the same table but this time retrieve the computed columns only. Once again I suggest you write down the result on the notepad.
Question 2: Guess the resultset
SELECT DT1C, DT2C FROM TableDT
Now here is the best part. Let us use the CAST function over the computed columns. Here I do want you to stop and guess the answer for sure. If you have not done it so far, stop do it, believe me you will like it.
Question 3: Guess the resultset
SELECT CAST(DT1C AS DATETIME2) CDT1C, CAST(DT2C AS DATETIME2) CDT1C FROM TableDT
Now let us inspect all the answers together and see how many of you got it correct.
If you have not tried to run the script so far, you can execute all the three of the above script together over here and see the result together.
SELECT CAST(DT1C AS DATETIME2) CDT1C, CAST(DT2C AS DATETIME2) CDT1C FROM TableDT
Here is the Saturday Fun question to you – why do we get same result from both of the expressions in Question 3, where as in question 2 both the expression have different answer. I will publish the valid answer with explanation in future blog posts.
Earlier I wrote one article on Leap Year and created one video about Leap Year. My point of view was to demonstrate how we can use SQL Server 2012 features to identify Leap year. How ever during the conversation I had some really good conversation. Here are updates for those who have missed reading the excellent comments on the blog.
There are so many people still think Leap Year is the event which is consistently happening at every four year and the way to find it is divide the year with 4 and if the remainder is 0. That year is leap year. Well, it is not correct.
“most years that are evenly divisible by 4 are leap years…”
“…Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Years that are evenly divisible by 100 are not leap years, unless they are also evenly divisible by 400, in which case they are leap years. For example, 1600 and 2000 were leap years, but 1700, 1800 and 1900 were not. Similarly, 2100, 2200, 2300, 2500, 2600, 2700, 2900 and 3000 will not be leap years, but 2400 and 2800 will be.”
If you use logic of divide by 4 and remainder is 0 to find leap year, you will may end up with inaccurate result. The correct way to identify the year is to figure out the days of February and if the count is 29, the year is for sure leap year.
I recently received the following question through email and I found it very interesting so I want to share it with you.
In SQL statement below the time difference between two given dates is 3 sec, but when checked in terms of Min it says 1 Min (whereas the actual min is 0.05Min)
SELECT DATEDIFF(MI,'2011-10-14 02:18:58' , '2011-10-14 02:19:01') AS MIN_DIFF
Is this is a BUG in SQL Server ?”
Answer is NO.
It is not a bug; it is a feature that works like that. Let us understand that in a bit more detail. When you instruct SQL Server to find the time difference in minutes, it just looks at the minute section only and completely ignores hour, second, millisecond, etc. So in terms of difference in minutes, it is indeed 1.
The following will also clear how DATEDIFF works:
SELECT DATEDIFF(YEAR,'2011-12-31 23:59:59' , '2012-01-01 00:00:00') AS YEAR_DIFF
The difference between the above dates is just 1 second, but in terms of year difference it shows 1.
If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it to minutes.
SELECT DATEDIFF(second,'2011-10-14 02:18:58' , '2011-10-14 02:19:01')/60.0 AS MIN_DIFF
Even though the concept is very simple it is always a good idea to refresh it. Please share your related experience with me through your comments.