Need to convert a TimeStamp column value to Simple Date Format

Database
Enthusiast

Need to convert a TimeStamp column value to Simple Date Format

Hi,

Current Q:
=============================================================================
Need to convert a TimeStamp column value to Simple Date Format.

I have a column in the database which is in time stamp format. And the date I am passing through parameter is:

EXTRACT_FROM_DT = Get it from some database table;
OR
EXTRACT_FROM_DT = CURRENT_DATE -7

So in both cases EXTRACT_FROM_DT which will be passed as parameter to where caluse of a SQL which is then compared to Timestamp column.
=============================================================================
Current Q END:
=============================================================================

Thanks.

This question is some what related to my last question. so I am gonna mention it here.

=============================================================================
Last Q:
==========================================================================
I need an SQL Function that gets me the 2nd Saturday of the month.

if
(Function (CURRENT_DATE) DAY) = 'SATURDAY' --Here I want the 2nd saturday
then
EXTRACT_FROM_DT = Get it from some database table;
else
EXTRACT_FROM_DT = CURRENT_DATE -7
fi
=============================================================================
Last Q END:
===========================================================================

If you are joedsilva Plz read below :-)
(I am sure joedsilva your are gonna reply. So Thanks in advance.)
6 REPLIES
Enthusiast

Re: Need to convert a TimeStamp column value to Simple Date Format


1. select cast(current_timestamp as date);

2.
select calendar_date from sys_calendar.calendar
where week_of_month = 2
and day_of_week = 7

TBob
Enthusiast

Re: Need to convert a TimeStamp column value to Simple Date Format

Bob has already given you the answer, except that I think week_of_month doesn't really get you to second Saturday because week_of_month is zero for the first partial week. The better approach is to use weekday_of_month = 2 as jgerstb mentioned in the other thread.

Enthusiast

Re: Need to convert a TimeStamp column value to Simple Date Format

Thanks TBob and joedsilva.

You Help was much appreciated :-)
N/A

Re: Need to convert a TimeStamp column value to Simple Date Format

Hi, when using cast(timestamp_expression as date) can i specify a date format?
Enthusiast

Re: Need to convert a TimeStamp column value to Simple Date Format

Yes, you can use the FORMAT.
Eg. SELECT CAST(current_timestamp as DATE FORMAT 'YYYYMMDD')

It is better to try in BTEQ rather than trying in SQL Assistant.

Regards,
Mm

Re: Need to convert a TimeStamp column value to Simple Date Format

This sample gets the value for the up-coming second saturday 

select top 1 calendar_date from sys_calendar.calendar

where  calendar_date > cast(current_date as date)

and week_of_month = 2 

and day_of_week = 7

order by 1 asc

Very useful for projecting things like back-up dates or future scheduling.