locked
BETWEEN with dates RRS feed

  • Question

  • Hello,

    I have a varchar column type that contains date in the format '01/23/2013'. I am using BETWEEN filter to get dates between '03/01/2012' to '04/30/2013' although it seems straight forward, I am not getting the right data because of the string. I tried casting and converting to datetime on both source and target. But I receive the following error. 

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

    I am trying to get the results with the following filter

    BETWEEN '03/01/' + CONVERT(varchar(15), YEAR(Getdate()) - 1) AND
    '04/30/' + CONVERT(varchar(15), YEAR(Getdate()) )

     

    I appreciate any ideas on this

    Thanks

    Hanu Ram

     
    • Moved by Kalman Toth Thursday, May 2, 2013 1:07 AM not db design
    Wednesday, May 1, 2013 8:35 PM

Answers

  • What is the reason for storing date in varchar column? Before it's too late, change the design of your table and store date information in date (or datetime for SQL 2005) type.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Hanu Ram Thursday, May 2, 2013 3:02 PM
    Thursday, May 2, 2013 2:07 AM
  • We have a  DATE data type; use it and quit writing SQL as if it were 1960's COBOL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Thursday, May 2, 2013 2:05 AM
    • Marked as answer by Hanu Ram Thursday, May 2, 2013 4:50 PM
    Thursday, May 2, 2013 1:13 AM

All replies

  • Hello,

    I have a varchar column that contains date in the format '01/23/2013'. I am using BETWEEN filter to get dates between '03/01/2012' to '04/30/2013' although it seems straight forward, I am not getting the right results. I tried casting and converting to datetime on both source and target. But I receive the following error. 

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

    I am trying to get the results with the following filter

    BETWEEN '03/01/' + CONVERT(varchar(15), YEAR(Getdate()) - 1) AND
    '04/30/' + CONVERT(varchar(15), YEAR(Getdate()) )

     

    I appreciate any ideas on this

    Thanks

    Hanu Ram

    • Merged by Naomi N Thursday, May 2, 2013 2:23 AM Same question
    Wednesday, May 1, 2013 10:58 PM
  • One way -

    WHERE CAST(YourVarcharColumn AS DATE)
    BETWEEN '20120301' AND '20130430'


    Narsimha

    Wednesday, May 1, 2013 11:51 PM
  • Convert to date or datetime data type, then use the >= and < operators instead of  BETWEEN.

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, May 2, 2013 1:06 AM
  • We have a  DATE data type; use it and quit writing SQL as if it were 1960's COBOL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Thursday, May 2, 2013 2:05 AM
    • Marked as answer by Hanu Ram Thursday, May 2, 2013 4:50 PM
    Thursday, May 2, 2013 1:13 AM
  • Try

    CONVERT(datetime,[date_col],101) BETWEEN CONVERT(datetime,'03/01/' + CONVERT(varchar(15), YEAR(Getdate()) - 1),101) AND
    CONVERT(datetime,'04/30/' + CONVERT(varchar(15), YEAR(Getdate()) ),101)

    But I also agree with Celko and Naomi that, you have to use Datetime column instead, as Date column with varchar datatype will lead to big problem with the index.


    Many Thanks & Best Regards, Hua Min


    Thursday, May 2, 2013 1:39 AM
  • What is the reason for storing date in varchar column? Before it's too late, change the design of your table and store date information in date (or datetime for SQL 2005) type.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Hanu Ram Thursday, May 2, 2013 3:02 PM
    Thursday, May 2, 2013 2:07 AM
  • You can try casting the date stored in string literal as Date,

    select * from sys.tables where create_date between cast ( '03/01/2012' as date )and  cast ('04/30/2013' as date)


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 2, 2013 2:10 AM
  • Hi,

    First of all, change the datatype of varchar column to date or datetime datatype, after which

    there will be no need for any conversion to date or datetime datatype.


    blue world

    Thursday, May 2, 2013 7:00 AM
  • This is what I tried in the first place

    SELECT X, [DateCol]
    FROM Table
    WHERE
    CONVERT(datetime, [DateCol],101) BETWEEN CONVERT(datetime,'03/01/' + CONVERT(varchar(15), YEAR(Getdate()) - 1),101) AND
    CONVERT(datetime,'04/30/' + CONVERT(varchar(15), YEAR(Getdate()) ),101)

    Where I get the varchar column for date from a pivot table.

    But keep receiving the following error

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

    Because of the indexing problem you mentioned. But I can see the status 'Query completed with errors' and '1035 rows' in the status panel at the bottom.

    With Naarasimha's method I get to see the data with additional number of rows...I am trying to analyse the problem here

    Many thanks for all of you.

    Thursday, May 2, 2013 12:40 PM
  • I think DateCol values may be in DD/MM/YYYY format. check once.

    ----

    Ravikanth

    Please vote if this solves your problem

    Thursday, May 2, 2013 1:12 PM
  • Hey Ram,

    Please have a look again your query is working fine as i have tested it on my side:

    Create Table #a(T varchar(50))

    insert into #a values( '01/02/2012' )

    insert into #a values('05/08/2013')

    insert into #a values( '01/02/2012' )

    insert into #a values( '05/02/2010')

    insert into #a values( '05/01/2010' )

    insert into #a values( '07/08/2011'

    )

    insert into #a values( '02/02/2012')

    insert into #a values( '08/05/2013' )

    Select * from #a

    SELECT T

    FROM #a

    WHERE

    CONVERT(datetime, T,101) BETWEEN CONVERT(datetime,'01/01/' + CONVERT(varchar(15), YEAR(Getdate()) - 1),101) AND

    CONVERT(datetime,'12/12/' + CONVERT(varchar(15), YEAR(Getdate()) ),101)

    So please tell me that the value in your column is in Data format as 'mm/dd/yyyy'


    Regards

    Vishal

    Thursday, May 2, 2013 1:13 PM
  • Its 'mm/dd/yyyy' and I resolved it using 

    Where [My Date Column] 
    BETWEEN 
    CAST ('03/01/' + CONVERT(varchar(15), YEAR(Getdate()) - 1) as date )and  
    CAST ('04/30/' + CONVERT(varchar(15), YEAR(Getdate()) ) as date)

    Vishal, I keep getting 'datetime data type resulted in an out-of-range value' value when using datetime conversion on both source field and target comparison. But it works fine when I tested the way you did. Thanks.. any ideas on why?

    Thursday, May 2, 2013 2:55 PM