You must Sign In to post a response.
  • Category: SQL Server

    How to convert var char value into date time format

    i have inserting var-char value like '3-2017' into column name (Test) . means month and year now i want to search records between '3-2017' to '5-2017' please help me how to fetch those records

    select *
    from Testable
    where Test > ='3-2017'
    and Test <='5-2017'

    i have tried above query but i'm not getting proper results

    Thanks,
    Advance.
  • #769246
    Change Varchar type into Date/Time.
    Insert Date in the format of Date/Time in your database.
    Then use above query.

  • #769249
    declare @to int, @from int, @year int,@fromyear int, @toyear int
    set @to=3
    set @from=5
    set @fromyear=2016
    set @toyear=2017
    select * from Test where SUBSTRING(Month_Of_Upload,1,1) between @to and @from and
    SUBSTRING(Month_Of_Upload,3,4)=@fromyear

    in the above querry if i tried 3-2016 to 5-2016 its wokring fine but if i tried 3-2017 to 5-2017 its not working

    here Month_Of_Upload is varchar value saving values like'3-2016'
    suppose if want fetch records between '3-2016' to '3-2017' its not wokring

  • #769320
    Hey Chanti,

    Below is well tested solution. I've tested with many Inputs it is giving 100% true results

    As your column has only month and year , so you need to add some day value to it .for example -'01' as I did in below example. So that u may convert it into Date format. This conversion is mandatory so that SQL server may understand behaviour of the column.


    declare @fromDate varchar(30)='03-2017',@endDate varchar(30)='05-2017'

    set @fromDate='01-'+@fromDate
    set @endDate='01-'+@endDate

    select
    * from yourTable
    where cast('01-'+ ColumnToSearch as date)
    between
    cast(@fromDate as date)
    and
    cast(@endDate as date)

    Thanks!
    Anjali Bansal

    ~Give your best and lead the world


  • Sign In to post your comments