date/time - 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 2005 Forums

SQL Server Administration (2005)

date/time

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-28 : 03:30:14

I want to compare a date with current date but when i use getdate() function, it adds time too, so the comparition doesnt work truly, is there any function that returns only current date(not time)?

thanks alot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 03:45:35

Only for SQL Server 2008.

You can use a number of equal checks. But without knowing the context of your query I'll just throw out some.

1) WHERE DATEDIFF(DAY, Col1, GETDATE()) = 0
2) WHERE Col1 >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND Col1 < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

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

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-28 : 04:00:35

I have SQL Server 2005 and it works on it too
thank alot

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-28 : 07:01:36

sorry, i have faced another problem:(
I want to use this command as a condition in 'WHERE',
i mean i want to see all the rows in my table which the date in Date filed of them equals current date.
but in the col part of datediff(day,col,getdate())we can have only one col .
what should i do ? please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 07:21:10

Use #2 above?

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

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-28 : 07:57:32

I want to have ALL rows which thie date field equals current date, how can i use #2 ?
SELECT * FROM table1 WHERE ... ?
I can use only one column there , isnt it ?

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2009-09-28 : 08:36:17

SELECT * FROM Table1 WHERE DateField = CONVERT ( VARCHAR ( 11 ) , GETDATE () , 106 )

--
Regards
Tony The DBA

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-29 : 00:59:25

thanks for your answer but it doesnt work, and returns empty table !

Kabila
Starting Member

33 Posts

Posted - 2009-09-29 : 01:08:00

Try this:

SELECT * FROM Table1 WHERE DateField = CONVERT ( VARCHAR ( 11 ) , GETDATE () , 101 )

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-29 : 01:11:07

select CONVERT ( VARCHAR ( 11 ) , GETDATE () , 106 ) returns: 29 sep 2009
and when i chose the type char(11) for date field, the date saves like : sep 29 2009

Is there any way to have this comparison without changing the field type to char ? i mean do comparison with date/time field ?

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-29 : 01:41:19

Thanks Kabila but again the two sides doesnt match,
select CONVERT ( VARCHAR ( 11 ) , GETDATE () , 101 ) returns :09/29/2009
select datefiels from table1 returns : 2009-10-02 08:58:24.970
select CONVERT ( VARCHAR ( 11 ) , GETDATE () , 106 ) returns: 29 sep 2009
and when i choose char(11) for field type,select datefiels from table1 returns :sep 29 2009

none of them match together:(

PS:I want to add some days to my date in my SP, so i have to use date/time as data type.
please help
thank you

Kabila
Starting Member

33 Posts

Posted - 2009-09-29 : 02:14:03

Try this:

SELECT * FROM Table1 WHERE CONVERT ( VARCHAR ( 11 ) , DateField, 101 )= CONVERT ( VARCHAR ( 11 ) , GETDATE () , 101 )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-29 : 02:31:37

SELECT * FROM Table1
WHERE Col1 >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND Col1 < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

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

Exir
Posting Yak Master

151 Posts

Posted - 2009-09-29 : 03:07:44