QUERY Correction

ammartahir1978
ammartahir1978 used Ask the Experts™
on
hi I am trying to extract data in 2005 SQL via query analyser, but i get this error

the conversion of the char datatype to deetermine data tuperesulted in an out of range datetime value

how can i fix this

SELECT     i_loc_code, i_dept, i_group, i_sub_grp_no, i_prod_code, i_promo, i_move_type, i_trans_date, i_move_ref, i_move_narrative, i_trans_qty, i_cost_val,
                      i_sell_val, i_move_per, i_year_no, i_audit_flag, i_reason_code, i_vat_val, zuser, i_com_stat, i_trans_time, BP_ROWID
FROM         stkmov
WHERE     (i_trans_date BETWEEN 01 / 01 / 2006 AND 31 / 12 / 2006)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT     i_loc_code, i_dept, i_group, i_sub_grp_no, i_prod_code, i_promo, i_move_type, i_trans_date, i_move_ref, i_move_narrative, i_trans_qty, i_cost_val,
                      i_sell_val, i_move_per, i_year_no, i_audit_flag, i_reason_code, i_vat_val, zuser, i_com_stat, i_trans_time, BP_ROWID
FROM         stkmov
WHERE     (i_trans_date BETWEEN '01 / 01 / 2006' AND '31 / 12 / 2006')

set date as String

Author

Commented:
hi Gowthamra,

thank you for replying, i have tried that but still get the same error.

any other idea.

Commented:
No spaces in your date, and best give an explicit conversion http://msdn.microsoft.com/en-us/library/ms187928.aspx

WHERE     (i_trans_date BETWEEN '01/01/2006' AND '31/12/2006')

WHERE     (i_trans_date BETWEEN CAST(Datetime,'01/01/2006',103)
             AND CAST(Datetime,'31/12/2006',103) )

Author

Commented:
Hi Jogos,

that didnt worked as well.

can you please put my query right?

Regards,

Author

Commented:
hi Guys,

i know its weired but this is the fix:)

i was thinking i am loosing the edge:

SELECT     i_loc_code, i_dept, i_group, i_sub_grp_no, i_prod_code, i_promo, i_move_type, i_trans_date, i_move_ref, i_move_narrative, i_trans_qty, i_cost_val,
                      i_sell_val, i_move_per, i_year_no, i_audit_flag, i_reason_code, i_vat_val, zuser, i_com_stat, i_trans_time, BP_ROWID
FROM         stkmov
WHERE     (i_trans_date >= '01/01/2010') AND (i_trans_date <= '01/01/2011')

Commented:
<<that didnt worked as well>>
Exact message?

Can you post table-definition of table stkmov

Commented:
<<WHERE     (i_trans_date >= '01/01/2010') AND (i_trans_date <= '01/01/2011')>>

So without spaces as I said ..... and with relevant dates (yeh)

Author

Commented:
hi All

here is the error msg

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


the query i am using is

SELECT     i_loc_code, i_dept, i_group, i_sub_grp_no, i_prod_code, i_promo, i_move_type, i_trans_date, i_move_ref, i_move_narrative, i_trans_qty, i_cost_val,
                      i_sell_val, i_move_per, i_year_no, i_audit_flag, i_reason_code, i_vat_val, zuser, i_com_stat, i_trans_time, BP_ROWID
FROM         stkmov
WHERE     (i_trans_date >= '02/01/2010') AND (i_trans_date <= '02/29/2010')


this above query worked for Jan month, but now give me error message for FEB.

Table definition


[i_loc_code] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_dept] [char](3)
      [i_group] [char](3)
      [i_sub_grp_no] [char](2) ,
      [i_prod_code] [char](16) ,
      [i_promo] [char](1) ,
      [i_move_type] [char](2) ,
      [i_trans_date] [datetime] NULL,
      [i_move_ref] [char](8) ,
      [i_move_narrative] [char](8) ,
      [i_trans_qty] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_cost_val] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_sell_val] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_move_per] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_year_no] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_audit_flag] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_reason_code] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_vat_val] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [zuser] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_com_stat] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [i_trans_time] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BP_ROWID] [int] IDENTITY(1,1) NOT NULL,
Commented:
<<WHERE     (i_trans_date BETWEEN '01/01/2006' AND '31/12/2006')>>
<<WHERE     (i_trans_date >= '02/01/2010') AND (i_trans_date <= '02/29/2010')


this above query worked for Jan month, but now give me error message for FEB.
>>
What is your date format DD/MM/YYYY or MM/DD/YYYY?

So back to my suggestion

"and best give an explicit conversion" http:/msdn.microsoft.com/en-us/library/ms187928.aspx


WHERE     (i_trans_date BETWEEN CAST(Datetime,'02/01/2011',101)
             AND CAST(Datetime,'12/31/2011',101) )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial