How do I... Convert date to datetime2 in SQL

Question

0.00/5 (No votes)

See more:

In my Table valued function, I have a variable of type date. I need to compare it (in a where clause) with the values of a column in a table, whose type is datetime2. Can anyone please help me with this, as the cast is not working for me.

What I have tried:

Following is my code:

SQL

select * from table1 where [Date1] >= cast(@Todate as datetime2)

Here, [Date1] is a column in table1 of type datetime2 and @Todate is of type date.

Posted 15-May-16 23:10pm

Bhavika Mdm

Updated 16-May-16 7:48am

Maciej Los

v2

Add a Solution

Comments

Tomas Takac 16-May-16 5:12am

What does it mean "the cast is not working for me"?

Bhavika Mdm 16-May-16 5:16am

I mean, when I try to execute the above query, I don't get any result, where as, I have many values for [Date1] that is greater then @Todate.

Simon_Whale 16-May-16 5:47am

can you show some example of what you mean by not working?

Bhavika Mdm 16-May-16 6:05am

I got the solution. The issue was that, the date part was getting converted to datetime2, but with time as 00:00:00. Then I did in the following way, and it worked.

select *
from Table1
where [Date1] >= DATEADD(MS,-1,CAST(DATEADD(DD,1,@toDate) AS datetime2))

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-05-16T07:48:00

OK, OP already has found solution:

Quote:
I got the solution. The issue was that, the date part was getting converted to datetime2, but with time as 00:00:00. Then I did in the following way, and it worked.

SQL
select *
from Table1
where [Date1] >= DATEADD(MS,-1,CAST(DATEADD(DD,1,@toDate) AS datetime2))

But i have to point out that - as MSDN documentation[^] states - an implicit conversion between date and datetime2 data types is possible by using CAST and CONVERT functions.

A datetime2 (Transact-SQL)[^] data type is well known as an extension of the existing datetime (Transact-SQL)[^] type that has a larger date range, a larger default fractional precision, and optional user-specified precision, but date[^] data type represents date without time part. So, the result of implicit conversion from date to datetime2 is date with time equal to zero!