using sql nvarchar(50) as date variable in sql server

Question

hi guys i have column that is Nvarchar(50) that i use it as a date type column because i use Persian calendar now i want to select times from two different time for example show me data s that are between 2012/12/18 and 2012/12/20 how could i do that please help and by the way i want use Stored Procedures for these actions i try this but it dose not return any thing

ALTER proc [dbo].[sel_monthReport] @fdate nvarchar(50),@sdate nvarchar(50),@para   nvarchar(50)
as
begin
declare  @parametr nvarchar(50)
set @parametr=@para
select * from main where @parametr>=@fdate and @parametr <=@sdate
end

Answer 1

Let's say you have a column in your main table called RecordDate, and this column is of type nvarchar, with the date format yyyy/mm/dd.

In that case, you don't need the @para parameter in your stored procedure. Change it to something like this:

ALTER PROC [dbo].[Sel_monthreport] @fdate NVARCHAR(50), 
                                   @sdate NVARCHAR(50) 
AS 
  BEGIN 
      SELECT * 
      FROM   main 
      WHERE  main.recorddate BETWEEN @fdate AND @sdate 
  END 

Note, that this is a NVARCHAR comparison, ant it only works because your dates are stored in the format yyyy/mm/dd. In general, a better approach would be to compare DATETIME datatypes (better performance and less error-prone).

You can convert an NVARCHAR value to a DATETIME datatype using the CONVERT function: http://msdn.microsoft.com/en-us/library/ms187928.aspx