Using of Global Variable

[system]

Hi All,

I am passing a value global variable as sysdate() which is having a fromat yyyy.mm.dd hh:mm:ss. I am using the global variable in the where clause and declaring for one column in query transformation. The column which is mapped to the source table column is having date filed of format mm/dd/yyyy. Please suggest how i can convert the sysdate format to mm/dd/yyyy so I can get the desired output.

Thanks,
Gourav

---

gourav (BOB member since 2010-05-22)

[system]

Things you should never do - a collection and discussion

Converting date, datetime datatypes: What format does a date datatype have, dd/mm/yyyy? dd.mm.yyyy? yyyy-dd-mm?? Answer: None. A date/datetime datatype has no format at all. Only if you use implicit conversion from date to char or vice versa, then you use the database session default format which can be different for each database, even each client connecting to the same database. Example: print(sysdate()); If you click on validate all you will see you have an implicit conversion from dateti…

Summary: sysdate() returns a native datetime datatype, it has no format. You can use to_char(sysdate(), ’ mm/dd/yyyy’) to convert a datetime into a string of your format.

---

Werner Daehn (BOB member since 2004-12-17)

[system]

Hi,

But the source field which i am mapping is a date feild,so how to proceed.

---

gourav (BOB member since 2010-05-22)

[system]

As said, a date does not have a format. You cannot say date <> date because the format is different. Either it is a date or a string. So let’s start one level higher: What is the error message you are getting?

---

Werner Daehn (BOB member since 2004-12-17)

[system]

If you want to compare a date and a datetime field you will have to convert either the datetime into a date or both to a string.

In your case you could use

to_date(to_char(sysdate(), 'dd-mm-yyyy'), 'dd-mm-yyyy')

as the date value you compare your source field to.

But actually that’s all already mentioned in the link Werner gave…

---

HerdplattenToni (BOB member since 2011-05-13)