CODEWITHSUNDEEP
c#sqlsql-server
MonaliJ
MonaliJ

Reputation: 223

How to deal with Date Format of SQL Server?

I have web application in asp.net 4.0 am using calender control for "FromDate" when am going to fetch my records then my query is as follows:

Select * 
From Sec_RoleFeatures
Where RoleId = 39 
  and FeatureCode = 'MR' 
  and FromDate='12/12/2012 11:05:05 AM'

as this `FromDate field date format does not match with sql format so result is that it does not give records give null.

so in this case what can I do? how to match my date format with sql date format?

Upvotes: 1

Views: 254

Answers (4)

Jon Skeet
Jon Skeet

Reputation: 1503429

Don't pass the value in the SQL at all. Use parameterized SQL for all values like this. Benefits:

  • The SQL is cleaner (no mixing of code and data)
  • Avoids SQL injection attacks (probably not relevant here, but definitely for strings)
  • Avoids conversion issues

Fundamentally, avoid string conversions where you can. Not just in database work, but in general. Try to keep your data in its "natural" type (DateTime here) for as long as possible. In this case you don't need to convert it to a string at all (as you can use parameters) so why do so?

See the documentation for SqlCommand.Parameters for an example of how to use parameters in your SQL.

Upvotes: 5

Novice
Novice

Reputation: 2487

Try Casting :

Select * From Sec_RoleFeatures Where RoleId = 39 and FeatureCode = 'MR' and 
CAST(FromDate AS DATE) = CAST('12/12/2012 11:05:05 AM' AS DATE)

The advantage here is, it will compare only the dates ignoring the time part.

Upvotes: 0

Patrick Guimalan
Patrick Guimalan

Reputation: 1010

try use

Select * From Sec_RoleFeatures Where RoleId = 39 and FeatureCode = 'MR' and  CONVERT(varchar(10),FromDate,101)='12/12/2012'

Upvotes: 0

SteB
SteB

Reputation: 2007

Use the reverse ISO format: yyyymmdd (so 12/12/2012 is 20121212) and use 24 hr format for time.

Select * From Sec_RoleFeatures Where (RoleId = 39) and (FeatureCode = 'MR') and (FromDate = '20121212 11:05:05')

Upvotes: 1

Related Questions