insert date only into SQL table

35 pts.
Tags:
Date Type Variables
SQL
The getdate() function returns the date and time. How can I just get the date?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’ll want to convert the value into just the date.

<pre>SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))</pre>This code converts the value into a character string of just the date, then converts that back to a datetime data type. The time will still be included, but it will be reset to midnight.

Another way to do it, which is somewhat more efficient than converting to string and back again, is to count the number of intervals between GETDATE() and some base date, then add that number of intervals back to the base date:

<pre>SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)</pre>Here the base date is 0 (i.e. Jan 1 1900 or whatever it is), and we count the number of days from the base date to today using DATEDIFF, then add them back. The result is the beginning of the interval, i.e. the beginning of today.

A similar formula can be used to give the beginning of other intervals, e.g. the first day of the month:

<pre>SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)</pre>The nice part about this approach is that date arithmetic is a lot faster than string conversion.

SQL Server 2008 is the first version of Microsoft SQL Server which includes the DATE and TIME data types which hold only the date or time not both.

You can read up on some of the new datatypes here: <a href=”http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266498,00.html”>What does the SQL Server 2008 July CTP offer?</a>.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following