comparing a column to Todays date

Question

comparing a column to Todays date

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 9, 2013 at 11:01 am

#282165

how do we compare a column name "LogDATE" to todays date and if its exists we print out a statement saying the record exists.
Ideally we would check the max row and check that the max row has todays date and if it does we print out a statement as " record exist no further action"
thanks
I tried this but it will give me 9/9 and 9/8 i just want 9/9 or just todays date
SELECT LogDATE
from Xlog
WHERE LogDATE >= Convert(datetime, Convert(int, GetDate()))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 1

SQLTestUser (9/9/2013)
how do we compare a column name "LogDATE" to todays date and if its exists we print out a statement saying the record exists.
Ideally we would check the max row and check that the max row has todays date and if it does we print out a statement as " record exist no further action"
thanks
I tried this but it will give me 9/9 and 9/8 i just want 9/9 or just todays date
SELECT LogDATE
from Xlog
WHERE LogDATE >= Convert(datetime, Convert(int, GetDate()))

Convert(datetime, Convert(int, GetDate())) computes to tomorrow's date -

Try this:

Convert(datetime, DATEDIFF(day, 0, GETDATE()))

TomThomson SSC Guru Points: 104773 More actions · Answer 2

The conversion to INT may be part of the problem. But Convert(datetime, Convert(int, GetDate())) should deliver midnight tonight, not midnight this morning, so it's surprising you are getting any rows at all if tomorrow's row doesn't exist.

Your description of what you want to do would require something like

IF EXISTS(

select 1 from Xlog where LogDATE >= cast(getDate() as date)

)

then PRINT 'Record already exists.' ;

Or you can select the LogDATE using slightly different code from what you have tried:

select top 1 LogDATE from Xlog

where LogDATE >= cast(getDate() as date)

order by LogDATE desc ;

If there are any rows for today or later that will deliver you just one LogDATE (the latest in the table), even if you have 10 rows in the table with LogDate today or later. Change desc to asc to get the earliest LogDATE in the table that is today or later (if there is any such row).

Tom

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 3

Convert(datetime, DATEDIFF(day, 0, GETDATE())) worked

IF EXISTS(

select 1 from Xlog where LogDATE >= cast(getDate() as date) -- should this be datetime if i have Sql server 2005?

)

then PRINT 'Record already exists.' ;

results in an error as

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'then'.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

SQLTestUser (9/9/2013)
Convert(datetime, DATEDIFF(day, 0, GETDATE())) worked
IF EXISTS(
select 1 from Xlog where LogDATE >= cast(getDate() as date) -- should this be datetime if i have Sql server 2005?
)
then PRINT 'Record already exists.' ;
results in an error as
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'.

Drop "THEN". That word is not valid in sql syntax.

IF EXISTS(

select 1 from Xlog where LogDATE >= cast(getDate() as date) -- should this be datetime if i have Sql server 2005?

)

PRINT 'Record already exists.' ;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 5

SQLTestUser

Hall of Fame

Points: 3997

September 9, 2013 at 2:35 pm

#1648657

that worked

Thanks,

TomThomson SSC Guru Points: 104773 More actions · Answer 6

SQLTestUser (9/9/2013)
-- should this be datetime if i have Sql server 2005?

No, if you are on 2005 you need something more complicated - as batgirl suggested,

convert(datetime, datediff(0, DD, getdate())) will work in SQL Server 2005 instead of cast(getdate() as date).

But if you were on 2005, why would you ask in the 2008 forum?

Sorry about that intrusive THEN that shouldn't have been there; had been working in a different language for a couple of hours, and somehow that then slipped from there into my sql.

Tom

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

SQLTestUser (9/9/2013)
Convert(datetime, DATEDIFF(day, 0, GETDATE())) worked
IF EXISTS(
select 1 from Xlog where LogDATE >= cast(getDate() as date) -- should this be datetime if i have Sql server 2005?
)
then PRINT 'Record already exists.' ;
results in an error as
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'.

Remove 'then' before PRINT, it will work

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/