x

today's date in in where clause

how do I refer to today's date in in where clause -- oracle construct = where xxx_dt < sysdate -- what is corresponding value to sysdate (informix = today)

more ▼

asked Nov 06, 2009 at 04:52 PM in Default

avatar image

tmorrow
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

getdate() returns current datetime value. ie: 2009-11-06 15:07:05.463

So if you want to select rows where a date column is anytime "today" then:

where DateCol >= dateadd(day, datediff(day, 0, getdate()), 0)            
and   DateCol < dateadd(day, datediff(day, 0, getdate()), 1)            

EDIT: That technique is usually better than converting the date to a varchar and then comparing that value to your datetime value because sql then needs to convert all the values to do the comparison.

more ▼

answered Nov 06, 2009 at 05:07 PM

avatar image

TG
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

The ANSI Standard is CURRENT_DATE for the date or CURRENT_TIMESTAMP for date and time. Virtually all SQLs support this now. Do not use the old getdate() that SQL Server got from Sybase, who got it from UNIX.

more ▼

answered Nov 07, 2009 at 04:35 PM

avatar image

Joe Celko
140 2 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x88

asked: Nov 06, 2009 at 04:52 PM

Seen: 1036 times

Last Updated: Nov 08, 2009 at 07:46 AM

Copyright 2017 Redgate Software. Privacy Policy