none
how to get data between two dates from sqlserver

    Question

  • Hi

    i write this query in sqlsrver but no data is retrieved  but i have data exists in tableHeader like this.

    ISSUEDATE

    2012-12-02 00:00:00.000

    select  convert(varchar,ISSUEDATE,103) from  tblHeader
    where (convert(varchar,ISSUEDATE,103) BETWEEN '12/12/2000' AND '02/12/2015')


    adil

    Tuesday, December 18, 2012 7:15 AM

Answers

All replies

  • Try this

    declare @tblHeader  table
    (ISSUEDATE datetime)
    insert into @tblHeader values('2012-12-02 00:00:00.000'),
    ('2012-11-02 00:00:00.000'),
    --No date for the month 10
    ('2012-09-02 00:00:00.000'),
    ('2012-08-02 00:00:00.000'),
    ('2012-07-02 00:00:00.000'),
    ('2012-06-02 00:00:00.000'),
    ('2012-05-02 00:00:00.000'),
    ('2012-04-02 00:00:00.000'),
    ('2012-03-02 00:00:00.000'),
    ('2012-02-02 00:00:00.000'),
    ('2012-01-02 00:00:00.000')
    
    
    select  convert(varchar,ISSUEDATE,103) from  @tblHeader 
    where ISSUEDATE BETWEEN CONVERT(datetime,'20121201') AND convert(datetime,'20121231')
    select  convert(varchar,ISSUEDATE,103) from  @tblHeader 
    where ISSUEDATE BETWEEN CONVERT(datetime,'20121101') AND convert(datetime,'20121130')
    select  convert(varchar,ISSUEDATE,103) from  @tblHeader 
    where ISSUEDATE BETWEEN CONVERT(datetime,'20121001') AND convert(datetime,'20121031')--No data here

    Regards
    Satheesh

    Tuesday, December 18, 2012 7:32 AM
  • No need to CONVERT

    SELECT * FROM tbl WHERE ISSUEDATE >='20001212' AND ISSUEDATE <='20151202'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, December 18, 2012 7:36 AM
  • It may be something to do with the formatting.

    Format 103 returns dd/mm/yyyy. you converted to string and searching between two strings. is that make sence.

    you are not searching the date between the dates.....for that reason your query not returning results.

    convert that to date and run you will see your records.

    thanks

    kumar

    Tuesday, December 18, 2012 8:52 AM
  • Use the following format for date range searches:

    select  convert(varchar,ISSUEDATE,103) from  tblHeader 
    where ISSUEDATE >=  '20001212' AND ISSUEDATE < '20150213';


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Server 2012 Programming

    Wednesday, December 19, 2012 1:26 AM