CODEWITHSUNDEEP
sql-servert-sqldatetime
user2059064
user2059064

Reputation: 163

Filter Date and Time in MS SQL Server used for Date Range filter

What is the correct Date Format should I use in MS SQL Server if I have this kind of scenario?

--This values here will be supplied by a datetimepicker Control
@StartDate = '05/15/2013 10:00 PM' 
@EndDate = '05/16/2013 06:00 AM'

SELECT * 
FROM tblSomething 
WHERE TransDate >= @StartDate AND TransDate <= @EndDate

How can I format the TransDate field?

Is this Correct? 

Declare @StartDate as datetime
Declare @EndDate as datetime

set @StartDate = '05/15/2013 10:00 PM'
set @EndDate = '05/16/2013 06:00 AM'


Select * from tblSomething Where convert(varchar(20),TransDate,100) >= convert(varchar(20),@StartDate,100) and convert(varchar(20),TransDate,100) <= convert(varchar(20),@EndDate,100)

The reason AM/PM is important to me is because we have employees that have transactions that starts 10PM in the evening and ends at 6am the next morning. I need to extract the transactions of that employee with that time frame.

Upvotes: 0

Views: 4361

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

If you need to think about date formats at all, you're doing it wrong.

The DateTimePicker control gives you a value of the .Net DateTime type. Your TransDate column should be a Sql Server DateTime type, as should your @StartDate and @EndDate parameters. At no point should you ever need to express any of these as a string. They should always be a binary format that isn't even human readable. If you've done this, the select query as written should do what you need.

Here's an example (C#, can do VB if you'd prefer) of how all this might work:

//imaginary function to check if there's at least one record in the data range
bool CheckDates(DateTime startDate, DateTime endDate)
{
    //sql is unchanged from your question
    string sql = "Select * from tblSomething Where TransDate >=  @StartDate  AND TransDate <= @EndDate";

     using (var cn = new SqlConnection("connection string here"))
     using (var cmd = new SqlCommand(sql))
     {
        cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;
        cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;

        cn.Open()
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
           //all we care is whether there's at least one row
           // there is a row if and only if .Read() succeeds
           return rdr.Read();
        }
    }
}

You could call it like this:

//hopefully you picked better control names :)
if (CheckDates(dateTimePicker1.Value, dateTimePicker2.Value))
{
   //do something
}

The important thing is that at no point anywhere do you ever convert that DateTime to a string.

Upvotes: 6

Related Questions