0

I am facing a problem selecting the date in SQL Server 2005. The table name is Softskill and the column name is insertdate. The data is stored as 2011-09-22 08:50:28.000 in this format.

To select, I am passing values from the front end as '2011-09-22', I mean the date only.

I tried to use 

SELECT INSERTDATE FROM SOFTSKILL WHERE INSERTDATE = '2011-09-22'.

But it is not showing the record. May I know which format the column is following and is there any way to retrieve data by using only date?

CharithJ
  • 46,289
  • 20
  • 116
  • 131
NAVEEN KUMAR
  • 1
  • 1

3 Answers3

3

You want to search for a range of times that encompass the day. Something like:

SELECT INSERTDATE 
    FROM SOFTSKILL 
    WHERE INSERTDATE >= '2011-09-22 00:00:00'
        AND INSERTDATE < '2011-09-23 00:00:00'

You could also apply functions to INSERTDATE to extract just the date portion, but that will make the query nonsargable.

Community
  • 1
  • 1
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
3
SELECT * 
FROM SOFTSKILL 
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, InsertDate)) = '2011-09-22'
CharithJ
  • 46,289
  • 20
  • 116
  • 131
0

This will do what you need

SELECT INSERTDATE 
FROM SOFTSKILL 
WHERE CAST(FLOOR(CAST(INSERTDATE AS FLOAT)) AS DATETIME) = '2011-09-30'

You need to truncate INSERTDATE so its time part is 00:00:00.000; then, it will be equal to '2011-09-30' assuming INSERTDATE is '2011-09-30 XX:XX:XX.xxx'.

daniloquio
  • 3,822
  • 2
  • 36
  • 56
  • Related useful question about truncating datetime: http://stackoverflow.com/questions/923295/how-to-truncate-a-datetime-in-sql-server – daniloquio Sep 30 '11 at 22:50