Date field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date field

Author

Topic

valerod
Starting Member

9 Posts

Posted - 2002-11-04 : 08:21:28

Hi!
I have a table with one field smalldatetime.
I want to searching in this field.
Ex:
Table
data
-----
10-10-2002 12:00:05

Query
Select * from table where data = '10/10/2002'

The query returns 0
How can do this?
Thanks
Valerod

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-11-04 : 08:28:30

The reason this does not work is because you are not taking into account the hour/minute/second part of the data. You need to write your select like so:

SELECT * FROM table WHRE data >= '10/10/2002'

***************************************
Death must absolutely come to enemies of the code!

neil
Starting Member

29 Posts

Posted - 2002-11-04 : 08:35:14

You could use the substring function to cut off the time, but you will need to convert this into varchar first.

SUBSTRING(CONVERT(VARCHAR(25),GETDATE(),103),1,10)

SELECT * FROM data WHERE
SUBSTRING(CONVERT(VARCHAR(10),data.datefield,103),1,10) = '20/10/2002'

valerod
Starting Member

9 Posts

Posted - 2002-11-04 : 10:24:38

If I convert the value of the field to varchar, how I will compare the date values. Ex;
Select * from table conver...(data) > '10/10/2002'

The query will compare text values not date values, right?
Thanks
Valerod

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-04 : 10:27:48

I would use something like

datediff (d,data, '10/10/2002') = 0

to check if it was the same day.

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-04 : 10:40:05

Sounds like (not sure) you want to look for items on a specific day, and sometimes greater or less than.

SELECT * FROM Mytable
WHERE CAST(CONVERT(VARCHAR, data, 101) AS DATETIME) = '10/10/2002'

See CONVERT in BOL for more information on the STYLE parameter (101). The 101 style removes the time from the datetime. The CAST converts the string back to DATETIME, resulting in all dates with time set to midnight..

The comparison operators < = > should work fine, because now the comparison is a datetime comparison, not a varchar comparison.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources