Mrdenny 
46795 pts. | Mar 6 2008 7:56PM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Q:
insert date only into SQL table
The getdate() function returns the date and time. How can I just get the date?
ASKED:
Mar 6 2008 5:50 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))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:
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)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:
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)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: What does the SQL Server 2008 July CTP offer?.
