SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ‘80040e07′ or Microsoft SQL Native Client error ‘80040e07′

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.

OR

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.

http://www.pinaldave.com/bimg/error-logo.jpgThe 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.

SELECT CONVERT (datetime, '01-Septeber-2013', 112)

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.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session.

“How do we know if today is a weekend or weekday using SQL Server Functions?”

Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012’s CHOOSE function. It is

SELECT GETDATE() Today,
DATENAME(dw, GETDATE()) DayofWeek,
CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
GO

You can use the choose function on table as well. Here is the quick example of the same.

USE AdventureWorks2012
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
GO

If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function.

Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Function to Round Up Time to Nearest Minutes Interval

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
AS
BEGIN
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.

Here are few sample examples.

SELECT dbo.roundtime('17:29',30)
SELECT dbo.roundtime(GETDATE(),5)
SELECT dbo.roundtime('2012-11-02 07:27:07.000',15)

When you run above code, it will return following results.

Well, do you have any other way to achieve the same result? If yes, do share it here and I will be glad to share it on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Retrieve SQL Server Installation Date Time

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
GO

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:

Retrieving SQL Server 2012 Evaluation Period Expiry Date

How to find the Installation Date for an Evaluation Edition of SQL Server

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SmallDateTime and Precision – A Continuous Confusion

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.

Now if you do not want to continue reading the blog post, head to my previous blog post over here: SQL SERVER – Precision of SMALLDATETIME.

A Social Media Question

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

An Alternate NO Solution

In the facebook thread this was also discussed as one of the solutions:

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?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Saturday Fun Puzzle with SQL Server DATETIME2 and CAST

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
GO

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
GO

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
GO

Now let us inspect all the answers together and see how many of you got it correct.

Answer 1:

Answer 2:

Answer 3: 

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
GO

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.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Various Leap Year Logics

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.

Incorrect Logic

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.

Comment by David Bridge

Check out this excerpt from wikipedia page

http://en.wikipedia.org/wiki/Leap_year

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

Valid Alternate Solutions

Comment by sainswor99insworth

IIF((@Year%4=0 AND @Year%100 != 0) OR @Year%400=0, 1,0)

Comment by Madhivanan

Madhivanan has written a blog post about an year ago where he listed multiple ways to find leap year.

Comment by Jayan

DECLARE @year INT
SET
@year = 2012
IF (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
PRINT ’1'
ELSE
print ’0'

Comment by David

DECLARE @Year INT = 2012
SELECT ISDATE('2/29/' + CAST(@Year AS CHAR(4)))

Comment by David Bridge

Incidentally – Another approach would be to take one day off March 1st and see if it is 29.

Reference: Pinal Dave (http://blog.sqlauthority.com)