how to compare dates in SQL query

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 2005 Forums

Transact-SQL (2005)

how to compare dates in SQL query

Author

Topic

sheetal_sarode
Starting Member

4 Posts

Posted - 2008-12-08 : 03:04:46

Hi All,

I have one table (OJDT) which has reddate field having datetime datatype. I want to write query on this table and check the condition on refdate.

I tried the following query
select transid from OJDT where convert(varchar(12),refdate,103) >= '01/12/2008' and convert(varchar(12),refdate,103) <= '31/12/2008'

but the result shows all data in the table.

Can anyone help to convert date to character format?

thanks & Regards,
Sheetal

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 03:06:08

Why are you converting a datetime to a STRING?

select transid from OJDT where refdate >= '01/12/2008' and refdate < '01/01/2009'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 03:18:18

just in case you pass parameter for start & end it will as fiollows


select transid from OJDT where refdate >= @Start and refdate < DATEADD(dd,1,@end)

sheetal_sarode
Starting Member

4 Posts

Posted - 2008-12-08 : 03:33:55

Hi,

If I donot convert to string I get error message as out of range values. start & end parameters will be key in by the user. SO I have to convert them to String.

Thanks & Regards,
Sheetal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 03:38:06

quote:
Originally posted by sheetal_sarode

Hi,

If I donot convert to string I get error message as out of range values. start & end parameters will be key in by the user. SO I have to convert them to String.

Thanks & Regards,
Sheetal

how are you passing date values through param? in what format? is parameter varchar or datetime?

sheetal_sarode
Starting Member

4 Posts

Posted - 2008-12-08 : 03:42:39

I am passing parameters in dd/mm/yyyy format. This parameter is varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 04:01:28

try running this before your query