selecting current date

[mannymo]

I have a simple select statement (see below). All i want is to pass the current date as an argument in my where clause.
below does not work. Any ideas?

select date_index
from history_master
where date_index = (select getdate())

Thanks

 

[onealm]

You don't need to select the current date.

If you use ASE, use this...
...
where date_index = Today(*)

If you use ASA, you can use Today(*) as above, or you can use Current Date, like this:
...
where date_index = Current Date

 

[mannymo]

Tried it, got below error.

select date_index
from history_master
where date_index = today(*)


Server Message: Number 195, Severity 15
Line 3:
'today' is not a recognized built-in function name.

 

[sybaseguru]

Try this

select date_index
from history_master
where date_index = getdate()

 

[mannymo]

Tried this too.

select date_index
from history_master
where date_index = getdate()

It returned column heading, but no data.
I also tried,

select date_index
from history_master
where date_index = convert(char (8),getdate())

It returns,
date_index
----------
Sep 10 2002 12:00AM
Sep 10 2002 12:00AM
Sep 10 2002 12:00AM
Sep 10 2002 12:00AM

Note 2002 data. There is 2003 data.
select date_index
from history_master
where date_index = '09/10/03'

date_index
----------
Sep 10 2003 12:00AM
Sep 10 2003 12:00AM
Sep 10 2003 12:00AM
Sep 10 2003 12:00AM
Sep 10 2003 12:00AM

 

[sybaseguru]

do sp_help history_master and send the output here

 

[toring]

The clue here is that the datetime datatype are accurate to 1/300 second. It holds both a datepart and a timepart. So when you compare values with the getdate() function you tries to find values stored in the same moment.

If you want to find values for a whole day in a datetime column you have to use a range between the start of the day and the end of the day.

If you add a value to a datetime column with no timepart it will get a default timepart 12:00AM

 

[mannymo]

sp_help history_master


Name Owner Type
---- ----- ----
history_master dbo user table

Data_located_on_segment When_created
----------------------- ------------
default Nov 7 1997 11:38AM

Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity
----------- ---- ----------- ----------- ----------- ----------- ------------ --------- -----------
order_number numeric 5 8 0 0 NULL NULL 0
shipment_nbr numeric 3 4 0 0 NULL NULL 0
line_number numeric 3 4 0 0 NULL NULL 0
item_code char 26 NULL NULL 1 NULL NULL 0
cust_id char 6 NULL NULL 1 NULL NULL 0
alt_ship_to numeric 2 2 0 1 NULL NULL 0
vendor_nbr char 6 NULL NULL 1 NULL NULL 0
prod_grp_code numeric 3 4 0 1 NULL NULL 0
order_type char 1 NULL NULL 1 NULL NULL 0
shipping_branch numeric 2 2 0 1 NULL NULL 0
invoice_qty numeric 6 10 2 1 NULL NULL 0
extended_sales_amt numeric 6 10 2 1 NULL NULL 0
extended_sales_cost numeric 6 10 2 1 NULL NULL 0
stock_flag numeric 2 2 0 1 NULL NULL 0
customer_po char 18 NULL NULL 1 NULL NULL 0
price_edit_flag numeric 2 2 0 1 NULL NULL 0
order_slm numeric 3 4 0 1 NULL NULL 0
order_taker numeric 3 4 0 1 NULL NULL 0
item_description char 27 NULL NULL 1 NULL NULL 0
ship_loc numeric 3 4 0 1 NULL NULL 0
date_index datetime 8 NULL NULL 1 NULL NULL 0
origin_ext_cost numeric 6 10 2 1 NULL NULL 0
ship_class_ord numeric 2 2 0 1 NULL NULL 0
ship_class_ren numeric 2 2 0 1 NULL NULL 0

index_name index_description index_keys index_max_rows_per_page index_fillfactor index_reservepagegap
---------- ----------------- ---------- ----------------------- ---------------- --------------------
idx_history_custid nonclustered located on default cust_id 0 0 0
idx_history_item nonclustered located on default item_code 0 0 0
idx_history_vendor nonclustered located on default vendor_nbr 0 0 0
pk_history_master clustered, unique located on default order_number, shipment_nbr, line_number 0 0 0
idx_date_index nonclustered located on default date_index 0 0 0

exp_row_size reservepagegap fillfactor max_rows_per_page
------------ -------------- ----------- -----------------
0 0 0 0

 

[mannymo]

Hi guys any more ideas?
Thanks

 

[grega]

Try

select date_index 
from history_master
where date_index = convert(char(8),getdate(),112)

Greg.

 

[mannymo]

Greg,
That worked!! Thank you.
The next question is why. What does the 112 do?
Manny

 

[sybaseguru]

mannymo,

For a complete explanation of date conversion, see ASE transact sql guide for "convert" function. In short, convert function can display the date in the format you want passeing the value.
Have a look at below for a complete list of display values
in convert(varchar(25),getdate(),<value>).
For example for <value> = 6 you will get

2> select convert(varchar(25),getdate(),6)
3> go

 -------------------------
 16 Sep 03


1> sp__date
2> go
0   Sep 16 2003  7:28PM           100  Sep 16 2003  7:28PM
1   09/16/03                      101  09/16/2003
2   03.09.16                      102  2003.09.16
3   16/09/03                      103  16/09/2003
4   16.09.03                      104  16.09.2003
5   16-09-03                      105  16-09-2003
6   16 Sep 03                     106  16 Sep 2003
7   Sep 16, 03                    107  Sep 16, 2003
8   19:28:30                      108  19:28:30
9   Sep 16 2003  7:28:30:376PM    109  Sep 16 2003  7:28:30:376PM
10  09-16-03                      110  09-16-2003
11  03/09/16                      111  2003/09/16
12  030916                        112  20030916

Hope this helps

 

[mannymo]

Thanks guys