Thread: returning the current date in a SQL query
I have a table (tblprojects) that stores the current projects in our facility. I want to be able to have two of the records "[unknown]" and "pending" (the project titles) always return the current date- basically overwriting the date field stored in the table. I need this because I am sorting the projects in a 'combo box' (in MS Access) by dates, for example today, this week, last week, this month, last month, etc. But I want the "[unknown]" and "pending" projects to ALWAYS appear in the result of the query. Is this possible? Do I have to set the date field of the table to something like a function? Thanks in advance for any help! Walt P.S. my date field is of type "datetime"
I think you have to play with UNION: select ... , project_date from tblprojects where title not in ( '[unknown]', 'pending' ) UNION ALL select ... , 'now'::datetime from tblprojects where title = '[unknown]' UNION ALL select ... , 'now'::datetime from tblprojects where title = 'pending'; It'll be more elegant if you create a VIEW with this query, but for the moment UNION is not available in VIEWs... Hope it helps... (dim) On Wed, 12 Aug 1998, you wrote: >I have a table (tblprojects) that stores the current projects in our >facility. I want to be able to have two of the records "[unknown]" and >"pending" (the project titles) always return the current date- basically >overwriting the date field stored in the table. > >I need this because I am sorting the projects in a 'combo box' (in MS >Access) by dates, for example today, this week, last week, this month, >last month, etc. But I want the "[unknown]" and "pending" projects to >ALWAYS appear in the result of the query. > >Is this possible? Do I have to set the date field of the table to >something like a function? > >Thanks in advance for any help! > >Walt > >P.S. my date field is of type "datetime" -- ===================================================== Dimitri KRAVTCHUK (dim) Sun Microsystems Benchmark Engineer France dimitri@France.Sun.COM =====================================================
> I think you have to play with UNION: > > select ... , project_date from tblprojects where title not in ( '[unknown]', > 'pending' ) > UNION ALL > select ... , 'now'::datetime from tblprojects where title = '[unknown]' > UNION ALL > select ... , 'now'::datetime from tblprojects where title = 'pending'; > > It'll be more elegant if you create a VIEW with this query, but > for the moment UNION is not available in VIEWs... > > Hope it helps... > (dim) yes, that is on the TODO list. Not sure it will be in 6.4. Depend on the rewrite system cleanups. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Cool, thanks for that information. This was not exactly what I was intending, but I can modify my query to do this. Basically I just wanted to ALWAYS see '[unknown]' and 'pending' on a result from a query that simply limited the range of dates in tblproject. But this does it perfectly / and better! Thanks again! -Walt On Wed, 12 Aug 1998, Dimitri wrote: > I think you have to play with UNION: > > select ... , project_date from tblprojects where title not in ( '[unknown]', > 'pending' ) > UNION ALL > select ... , 'now'::datetime from tblprojects where title = '[unknown]' > UNION ALL > select ... , 'now'::datetime from tblprojects where title = 'pending'; > > It'll be more elegant if you create a VIEW with this query, but > for the moment UNION is not available in VIEWs... > > Hope it helps... > (dim) > > On Wed, 12 Aug 1998, you wrote: > >I have a table (tblprojects) that stores the current projects in our > >facility. I want to be able to have two of the records "[unknown]" and > >"pending" (the project titles) always return the current date- basically > >overwriting the date field stored in the table. > > > >I need this because I am sorting the projects in a 'combo box' (in MS > >Access) by dates, for example today, this week, last week, this month, > >last month, etc. But I want the "[unknown]" and "pending" projects to > >ALWAYS appear in the result of the query. > > > >Is this possible? Do I have to set the date field of the table to > >something like a function? > > > >Thanks in advance for any help! > > > >Walt > > > >P.S. my date field is of type "datetime" > -- > ===================================================== > Dimitri KRAVTCHUK (dim) Sun Microsystems > Benchmark Engineer France > dimitri@France.Sun.COM > ===================================================== >
At 21:31 +0300 on 12/8/98, Walt Bigelow wrote:
>
> I have a table (tblprojects) that stores the current projects in our
> facility. I want to be able to have two of the records "[unknown]" and
> "pending" (the project titles) always return the current date- basically
> overwriting the date field stored in the table.
You can use the value 'current' for the date field in your table. 'current'
is a value which is always translated in comparisons and other expressions
to the current date and time - as opposed to 'now', which is only current
for the time of insertion.
Here is an example table, with only one field, d, of type datetime:
testing=> select * from test7;
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
(3 rows)
testing=> insert into test7 values ('now');
INSERT 788160 1
testing=> insert into test7 values ('current');
INSERT 788161 1
testing=> select * from test7;
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Sun Aug 16 14:02:53 1998 IDT
current
(5 rows)
As you can see, the row where I entered 'current' displays 'current' rather
than the actual date. This can be overcome easily. But for now, the point
is that the 'current' field stays current. Here, I'll try a query for all
dates after today at 14:00:
testing=> select * from test7
testing-> where d > '1998-08-16 14:00';
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
Sun Aug 16 14:02:53 1998 IDT
current
(3 rows)
As you can see, the row with the 'current' value as well as the one which
was 'now' have been selected. Now, to show the difference, I'll select
everything after today at 14:03. This should drop the row which was 'now':
testing=> select * from test7
testing-> where d > '1998-08-16 14:03';
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
current
(2 rows)
See? Only the 'current' row and the row with the date in the future were
selected. This shows that 'current' continues to update.
So, you ask, how do I get the actual date and not the annoying 'current' in
the output? Very easy. You just use an expression instead of the field -
add zero to the field, and 'current' will be converted to a date:
testing=> select d + '0 days' from test7;
?column?
----------------------------
Tue Dec 01 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Sun Aug 16 14:02:53 1998 IDT
Sun Aug 16 14:13:16 1998 IDT <---- This is the 'current' row.
(5 rows)
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma