Play around the Datetime data
-
Hi,
I'm beginner of Ms SQL Server.
Let says, i've these following table.
ID | Message | Date_and_Time|
---------------------------------------------
1 | Log-in | 4/4/2006 4:34:43 pm
1 | Log-out | 4/4/2006 2:45:34 pm
1 | Log-in | 5/4/2006 10:02:46 am
1 | Log-out | 5/4/2006 12:02:41 pm
2 | Log-in | 5/8/2006 10:23:12 am
2 | Log-out | 5/8/2006 12:45:12 pm
3 | Log-in | 6/4/2006 2:34:12 pm
3 | Log-out | 6/4/2006 3:23:34 pm
My Date_and_Time (mm/dd/yyyy) datatype is datetime.
Question : I need to retrieve the data in May, 2006 (6/2006) onwards only. How to query?
The expected output shown below,
ID | Message | Date_and_Time|
---------------------------------------------
1 | Log-in | 5/4/2006 10:02:46 am
1 | Log-out | 5/4/2006 12:02:41 pm
2 | Log-in | 5/8/2006 10:23:12 am
2 | Log-out | 5/8/2006 12:45:12 pm
3 | Log-in | 6/4/2006 2:34:12 pm
3 | Log-out | 6/4/2006 3:23:34 pm
Please help me
-
Hi,
If you want to select everything for a particular month use:
SELECT * FROM <TABLE_NAME> WHERE MONTH(Date_and_Time) = 6
Note you can also use the following:
DAY(Date_and_Time)
YEAR(Date_and_Time)
Hope this Helps,
A
-
Try this one:
SELECT [ID], Message, Date_and_Time
FROM <Table Name>
WHERE Date_and_Time >= CONVERT(DATETIME, '20060501')
If your table is indexed on Date_and_Time, this should be able to use that index.
--Andrew
-
an extra vote for Andrew's solution since it allows index usage
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply