dataset ?

• SQL_path

  SSCrazy

  Points: 2810

  More actions

  May 9, 2012 at 11:39 am

  #268632

  Hi all,

  My query has data that drives off of @reportstart @reportend but for the same paramerters the dataset in the report runs blank, therefore available values are empty. What could be the reason.

  thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 9, 2012 at 11:49 am

  #1485229

  Not sure. We can't see what you see. Really need more information to give you an real answer.

• Lowell

  SSC Guru

  Points: 323546

  More actions

  May 9, 2012 at 12:48 pm

  #1485262

  SQL_path (5/9/2012)

  ---

  Hi all,

  My query has data that drives off of @reportstart @reportend but for the same paramerters the dataset in the report runs blank, therefore available values are empty. What could be the reason.

  thanks.

  wild guesses: make sure ALL fields and paramters are datetimes; if any of them are varchars, your statement might return no rows.

  make sure your parameters actually have values whn they hit the query; if they were null, you'd get no data either.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQL_path

  SSCrazy

  Points: 2810

  More actions

  May 10, 2012 at 9:32 am

  #1485789

  Thank you all. That was the case,

  Now I am struggling with bad data , would appreciate your help. I want to convert following data in datetime hhmmss format Here is what the data looks like

  I have following data as

  create table #123

  (dates varchar(25) null)

  INSERT INTO #123 (dates)

  SELECT '1/4/1996'

  UNION ALL

  SELECT ' 10/11/1996'

  UNION ALL

  SELECT '1/1/08'

  UNION ALL

  SELECT '12/16/11'

  UNION ALL

  SELECT '5-02-11'

  union all

  SELECT '05-10-11'

  select * from #123

  ----------I tried to convert as following but getting an error---Conversion failed when converting date and/or time from character string.

  select convert (datetime,ltrim(rtrim(dates)), 101) from #123

  drop table #123

  thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 10, 2012 at 9:43 am

  #1485796

  The following works on my pc.

  create table #123

  (dates varchar(25) null);

  go

  INSERT INTO #123 (dates)

  SELECT '1/4/1996'

  UNION ALL

  SELECT ' 10/11/1996'

  UNION ALL

  SELECT '1/1/08'

  UNION ALL

  SELECT '12/16/11'

  UNION ALL

  SELECT '5-02-11'

  union all

  SELECT '05-10-11';

  select * from #123 ;

  ----------I tried to convert as following but getting an error---Conversion failed when converting date and/or time from character string.

  select convert (datetime,ltrim(rtrim(dates))) from #123;

  go

  drop table #123;

  go

• Lowell

  SSC Guru

  Points: 323546

  More actions

  May 10, 2012 at 9:51 am

  #1485803

  i see it does fail if your DATEFORAMT is DMY and not MDY: i get the same error you got when i explicitly set my dateformat:

  (/12//16/11 causes the error: no month 16 exists)

  SET DATEFORMAT DMY

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQL_path

  SSCrazy

  Points: 2810

  More actions

  May 10, 2012 at 9:56 am

  #1485807

  thank you,

  that worked for the data I posted, I guess I still have some bad data, as I still get the same error any tips on how to locate that in some 4000 number of rows.

  i tried searching as -

  where rtrim( ltrim(dates)) like '%-%' or dates like '%/%'

  thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 10, 2012 at 10:04 am

  #1485820

  SQL_path (5/10/2012)

  ---

  thank you,

  that worked for the data I posted, I guess I still have some bad data, as I still get the same error any tips on how to locate that in some 4000 number of rows.

  i tried searching as -

  where rtrim( ltrim(dates)) like '%-%' or dates like '%/%'

  thanks

  Not off hand. That is one of the problems with storing dates as character strings.

  Oh, wait. Try running a query that returns rows where isdate([DateStringColumn]) = 0. It should find some of them.

• SQL_path

  SSCrazy

  Points: 2810

  More actions

  May 10, 2012 at 10:46 am

  #1485844

  Thank you so much..it worked. 🙂