December 2, 2008 at 7:16 am
I am trying to run a simple SELECT query to select just three dates from a table. The date column also has the time in it as well. How do I run a query just to select the dates without the time?
December 2, 2008 at 7:20 am
You can convert or cast it to a VARCHAR and strip off the time:
SELECT CONVERT(VARCHAR,GETDATE(),101)
Look up CAST or CONVERT in books online.
If this is just for display purposes, stripping time off of datetime fields is something that should be done in the client application as a display option using the user's local date/time display settings.
December 2, 2008 at 7:39 am
Another option is as follows:
declare @TestTbl table (
Date1 datetime,
Date2 datetime,
Date3 datetime
);
insert into @TestTbl
select '2008-11-11 12:00:00.000', '2008-11-12 08:30:34.333', '2008-11-13 14:12:15.000';
select
Date1,
Date2,
Date3,
dateadd(dd, datediff(dd, 0, Date1), 0) as Date1a,
dateadd(dd, datediff(dd, 0, Date2), 0) as Date2a,
dateadd(dd, datediff(dd, 0, Date3), 0) as Date3a
from
@TestTbl;
December 2, 2008 at 7:55 am
And here is one more option:
select cast(Floor(cast(Getdate() as float)) as datetime)
😀
December 3, 2008 at 8:13 am
There's been many discussions about this on the forums.
The consensus was that the method Lynn mentioned is the fastest.
SELECT DATEADD(dd,0, DATEDIFF(dd,0,GetUTCDate()))
December 3, 2008 at 8:29 am
I appreciate everyone's responses regarding the date_time field. After talking with one of my fellow DBA's I used the BETWEEN command to select the dates. Here is my query. I wanted to select 9/29, 10/2 and 10/3.
SELECT *
FROM dbo.tbl_dates
WHERE (date_time BETWEEN '2008-09-29' AND '2008-09-30')
OR (date_time BETWEEN '2008-10-02' AND '2008-10-04')
December 3, 2008 at 8:51 am
rshafer (12/3/2008)
I appreciate everyone's responses regarding the date_time field. After talking with one of my fellow DBA's I used the BETWEEN command to select the dates. Here is my query. I wanted to select 9/29, 10/2 and 10/3.SELECT *
FROM dbo.tbl_dates
WHERE (date_time BETWEEN '2008-09-29' AND '2008-09-30')
OR (date_time BETWEEN '2008-10-02' AND '2008-10-04')
This will also pull records with a date_time = '2008-09-30 00:00:00.000' or date_time = '2008-10-04 00:00:00.000'.
This is what you would want for your WHERE clause:
WHERE
(date_time >= '2008-09-29' AND date_time < '2008-09-30')
OR (date_time >= '2008-10-02' AND date_time < '2008-10-04')
December 3, 2008 at 11:56 am
Lynn Pettis (12/3/2008)
rshafer (12/3/2008)
I appreciate everyone's responses regarding the date_time field. After talking with one of my fellow DBA's I used the BETWEEN command to select the dates. Here is my query. I wanted to select 9/29, 10/2 and 10/3.SELECT *
FROM dbo.tbl_dates
WHERE (date_time BETWEEN '2008-09-29' AND '2008-09-30')
OR (date_time BETWEEN '2008-10-02' AND '2008-10-04')
This will also pull records with a date_time = '2008-09-30 00:00:00.000' or date_time = '2008-10-04 00:00:00.000'.
This is what you would want for your WHERE clause:
WHERE
(date_time >= '2008-09-29' AND date_time < '2008-09-30')
OR (date_time >= '2008-10-02' AND date_time < '2008-10-04')
I orignally was using the greater than and less than operators before talking to my coworker. How is it when I use the BETWEEN statement that is did not reutrn 9/30 and 10/4? I had the same thinking as you, but SQL only returned 9/29, 10/2 and 10/3.
December 3, 2008 at 12:00 pm
You did not see any results for the end dates because you specified exactly midnight of that day.
You did not have any records at exactly midnight on 10/4. However, if you did have records at that time it would have been returned.
December 3, 2008 at 12:19 pm
ggraber already answered your question. The best thing I could suggest at this time is for you to test it yourself. All you need to do is create a small table with one column, a datetime, insert some values, including some at midnight and check it out the difference between the two methods.
December 3, 2008 at 12:42 pm
ggraber (12/3/2008)
You did not see any results for the end dates because you specified exactly midnight of that day.You did not have any records at exactly midnight on 10/4. However, if you did have records at that time it would have been returned.
Ggraber you are correct. Just tested it on my machine and when I inserted a row for 9/30 and 10/4 with a time of midnight it did include those dates. Thanks again for everyones help.
December 3, 2008 at 12:59 pm
rshafer (12/3/2008)
Thanks again for everyones help.
You're welcome, and thanks for the feedback.
December 11, 2008 at 12:49 pm
Just learned that you can use the LIKE command to select individual months. You have to use the three letter abbreviation and enclose it in percent signs. A handy little statement I didn't know about.
SELECT *
FROM items_ordered
WHERE order_date LIKE '%dec%'
December 11, 2008 at 4:11 pm
December 11, 2008 at 4:32 pm
rshafer (12/3/2008)
I appreciate everyone's responses regarding the date_time field. After talking with one of my fellow DBA's I used the BETWEEN command to select the dates. Here is my query. I wanted to select 9/29, 10/2 and 10/3.SELECT *
FROM dbo.tbl_dates
WHERE (date_time BETWEEN '2008-09-29' AND '2008-09-30')
OR (date_time BETWEEN '2008-10-02' AND '2008-10-04')
Seems you need this:
SELECT SUBSTRING((CONVERT(varchar, getdate(), 101)),1,5)
12/12
SELECT SUBSTRING((CONVERT(varchar, date_time, 101)),1,5)
FROM dbo.tbl_dates
WHERE (date_time BETWEEN '2008-09-29' AND '2008-09-30')
OR (date_time BETWEEN '2008-10-02' AND '2008-10-04')
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply