All Forums Database
imranishaque 15 posts Joined 02/07
14 Feb 2007
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.)

RGlass 35 posts Joined 09/04
14 Feb 2007

1. select cast(current_timestamp as date); calendar_date from sys_calendar.calendarwhere week_of_month = 2and day_of_week = 7 TBob

joedsilva 505 posts Joined 07/05
14 Feb 2007

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.

imranishaque 15 posts Joined 02/07
15 Feb 2007

Thanks TBob and joedsilva.You Help was much appreciated :-)

fridi 1 post Joined 07/08
23 Jul 2008

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

Meem 24 posts Joined 05/07
24 Jul 2008

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

29 May 2014

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.

~ it's all just string manipulation ~

You must sign in to leave a comment.