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?
Don't pass the value in the SQL at all. Use parameterized SQL for all values like this. Benefits:
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.
YYYYMMDD in case the date entry is entered directly, not from a C# application.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')
try use
Select * From Sec_RoleFeatures Where RoleId = 39 and FeatureCode = 'MR' and CONVERT(varchar(10),FromDate,101)='12/12/2012'
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.
