Date Format in SSRS

• SQL DBA-685905

  Say Hey Kid

  Points: 670

  More actions

  July 11, 2008 at 2:32 am

  #188065

  Hi..

  I am facing a problem with Date format in SSRS. I created a report where it accepts the date as input where the date format is in dd/mm/yyyy and yyyy/mm/dd.

  What i want is, it has to accept the mm/dd/yyyy format or any given date format.

  My code is :

  WHERE

  logondate >= convert(datetime,(convert(varchar,Datepart(yyyy,DATEADD(mm,-3,'19/06/2008'))) + '-' + convert(varchar,Datepart(mm,DATEADD(mm,-3,'19/06/2008'))) + '-' + convert(varchar,Datepart(mm,DATEADD(mm,-3,'19/06/2008')))),3)

  and logondate <= ('19/06/2008')

  It is not accepting the given date '19/06/2008'..and raising an error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

  Please help me out..

  Thanks..

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  July 11, 2008 at 6:49 am

  #841424

  pswathivineela (7/11/2008)

  ---

  Hi..

  WHERE

  logondate >= convert(datetime,(convert(varchar,Datepart(yyyy,DATEADD(mm,-3,'19/06/2008'))) + '-' + convert(varchar,Datepart(mm,DATEADD(mm,-3,'19/06/2008'))) + '-' + convert(varchar,Datepart(mm,DATEADD(mm,-3,'19/06/2008')))),3)

  and logondate <= ('19/06/2008')

  You can use DATEFORMAT to change the format of date.

  The following example uses different date formats to handle date strings in different formats:

  -- Set date format to Month, Day, Year

  SET DATEFORMAT mdy

  GO

  DECLARE @datevar DATETIME;

  SET @datevar = '12/25/2007';

  SELECT @datevar AS DateVar;

  GO

  -- Set date format to Year, Day, Month.

  SET DATEFORMAT ydm;

  GO

  DECLARE @datevar DATETIME;

  SET @datevar = '2007/25/12';

  SELECT @datevar AS DateVar;

  GO

  -- Set date format to Year, Month, Day.

  SET DATEFORMAT ymd;

  GO

  DECLARE @datevar DATETIME;

  SET @datevar = '2007/12/25';

  SELECT @datevar AS DateVar;

  GO

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers