>>> Programming >> MSSQL > How to select from SQL based on Dates? (This page has been seen 605 times)

How to select from SQL based on Dates?

Selecting something from SQL Server based on Dates can be quite difficult. Always make sure to make your date fields in your table to an actual datetime field. This will do that SQL can use its command with it.

Another thing i always does when creating a new Table is to make a Field called CreatedDate. and then assigning a default value to it. The default value will be (getdate()) This means that everytime you insert a record into your table, SQL server will always automatically insert a TimeStamp into that field, so you can always se when people has created the record, down to the millisecond.

Now getting into selecting with a date filter.

SELECT * FROM MyTable WHERE CreatedDate = '01/01/2008'

Now we would think that this would return us all records created '01/01/2008' right? WRONG. This cannot be done that way, and alot of people try to do it that way and never get a result back. The reason for this is that the datetime field is stored with the Date AND the TIME when you insert/update etc. So the Field CreatedDate actually contains '00/00/0000 00:00:00:000'. So you cannot select from the field that way. However you could say

SELECT * FROM MyTable WHERE CreatedDate = '01/01/2008 10:00:00:000'

But this will only give you records that is created at 01/01/2008 at 10:00:00:000

So a better approach is this

SELECT * FROM MyTable WHERE DATEPART(Day,CreatedDate) 01 AND DATEPART(Month,CreatedDate) = 01 AND DATEPART(Year,CreatedDate) = 2008

This will give you the result you are looking for. DATEPART can do alot of good stuff for you. Here is a reference

year
quarter
month
dayofyear
day
week
weekday
hour
minute
second
millisecond

You can also you a command called BETWEEN. This defines a DATERANGE. So you could do something like this

SELECT * FROM MyTable WHERE CreatedDate BETWEEN '01/01/2008' AND '01/10/2008'

Remember that this is called BETWEEN. This means in our example it will not return the dates. 01/01/2008 and 01/10/2008. If you want those dates included you can do it like this

SELECT * FROM MyTable WHERE CreatedDate >= '01/01/2008' AND CreatedDate <= '01/10/2008'

This means that if CreatedDate is BIGGER OR EQUAL TO 01/01/2008 Then Return it, and the same goes the other way, IF CreatedDate is SMALLER OR EQUAL TO 01/10/2008 Then return it.

Like (1)

Dislike (0)

Keywords for this article:
DATEPART || BETWEEN || DATETIME || SQL || TSQL

Post comment

Name:

Comment:

Code Language:

Code:

Here you can paste a code example. It will then be processed by SyntaxHighlighter and formatted for easier readability.
Please remember to select the correct Code Language in the select above so the SyntaxHighlighter can highlight the code properly.

Code:

Please enter the code you see above

What is 5 + 4 =

Submit Comment

No one has commented on this page yet. You could be the first.

Advertisement by Google