Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: SQL Server 2005 simple select statement not working

    Hi all,

    I need your help.
    I have a table where there are 53 columns.
    Instead of designing multiple tables with primary key foreign key relationships, we used a single table where most of the columns will be null if that column is not required for the data which we enter.

    Recently we encountered a problem where a simple select statement is not retrieving data.

    Select * from test_Table where Createddate ='2/18/2012' is not retrieving any data where it used to fetch rows earlier. from the past one week we are not able to use simple select statement in this table alone.

    For your reference I am posting the table design, Kindly help me on this issue and provide a fix for the same.

    please find the snapshot attached with this which will give a clear idea of what problem i am facing now

    Need your help

    Thanks in Advance,
    Johny
    Attached Thumbnails Attached Thumbnails snap1.png   snap2.png  
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    None of those dates equal 2/18/2012 which is equivalent to 2012-02-18 00:00:00 (note the time portion).

    Try this
    Code:
    WHERE  your_date_field >= '20120218'
    AND    your_date_field <  '20120219'
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Hi Gvee,

    Thanks for your reply. I have date which is equal to 2/18/2012. I even tried different date formats and also with the time portion but still this is not working.
    Earlier i Was able to retrieve just giving the date but now for the past two weeks it is not fetching any rows.

    Please find the screenshots which i have attached.
    In 1st screenshot i was not getting any when i used equal operator.
    but in the 2nd screenshot i was using a range where i get rows with the following date in it.

    Please give a solution for my problem.


    Regards,
    Johny

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL Server considers both the date and the time in comparisons, and all of the data you displayed includes time values. You could strip the time by using an expression, but the solution provided by gvee is actually the most efficient way to accomplish what you want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2012
    Posts
    3

    Smile User defined function

    Hi Pat And George,

    Thanks for your help. Can i use an user-defined function which directly takes out the date part alone without the time?

    Please also suggest if there any system defined function which only takes out the date part without time
    Ex: 15/3/2012 06:36:21 to 15/3/2012


    Thanks in Advance,
    Johny

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT GetDate() As date_inc_time
         , DateAdd(dd, DateDiff(dd, 0, GetDate()), 0) As just_the_date
    
    --If you're using SQL2008 on
    SELECT GetDate() As date_inc)time
         , Convert(date, GetDate()) As just_the_date_sql_2008_on
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As gvee demonstrated, it is possible to trim the time from a DATETIME value.

    If you have infinite time to run your queries or truly massive hardware (around a million US Dollars worth), trimming the DATETIME to a DATE value is an easy solution to your problem.

    With an appropriate index, gvee's original solution from post #2 above works well on a laptop and even better on a server. I would use it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •