cancel
Showing results for 
Search instead for 
Did you mean: 

Converting Date to Char

Former Member
0 Kudos

Using MaxDB Version Version: 7.7.04.28

I am seeing difficulties with the CHAR(a,t) function. I have an SQL statement where I would like to convert the current date to a string and pick only the year information.

If I understand the documentation correctly, this should work: CHAR(NOW(),'YYYY')

However, I find that the second parameter is not a year but a locale.

This works: CHAR(NOW(),USA) and gives 2009-10-09

So to get the year information I am doing this: SUBSTR(CHAR(NOW(), USA),1,4)

But this looks like a hack. The clean way would be to use CHAR(NOW(),'YYYY'). How come this does not work?

Many thanks for any help.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks Thanks Thanks

lbreddemann
Active Contributor
0 Kudos

> I am seeing difficulties with the CHAR(a,t) function. I have an SQL statement where I would like to convert the current date to a string and pick only the year information.

>

> If I understand the documentation correctly, this should work: CHAR(NOW(),'YYYY')

You don't understand this command correctly, sorry.

> However, I find that the second parameter is not a year but a locale.

> This works: CHAR(NOW(),USA) and gives 2009-10-09

Yes, sure it works.

The documentation says that exactly this would happen when you use the command as you did.

Problem in sight?

> So to get the year information I am doing this: SUBSTR(CHAR(NOW(), USA),1,4)

> But this looks like a hack. The clean way would be to use CHAR(NOW(),'YYYY'). How come this does not work?

What you do there is a hack, but it's not about the data extraction model.

There is a specific function for what you want to to: YEAR().

Have a look at

[Extraction (extraction_function)|http://maxdb.sap.com/doc/7_7/45/583ea6484365d1e10000000a1553f6/content.htm]

and the bunch of SQL Tutorials on this topic you also find in the documentation.

Just as a general hint: don't try to do Oracle with any database that is not Oracle.

Examples:


sqlcli db77=> select year(now()) from dual
| EXPRESSION1        |
| ------------------ |
|               2009 |

1 row selected (168.846 msec)

sqlcli db77=> select month(now()) from dual
| EXPRESSION1        |
| ------------------ |
|                 10 |

1 row selected (1406 usec)

sqlcli db77=> select day(now()) from dual
| EXPRESSION1        |
| ------------------ |
|                 10 |

1 row selected (1234 usec)

sqlcli db77=> select dayofweek(now()) from dual
| EXPRESSION1        |
| ------------------ |
|                  6 |

1 row selected (1671 usec)

sqlcli db77=> select weekofyear(now()) from dual
| EXPRESSION1        |
| ------------------ |
|                 41 |

1 row selected (885 usec)

sqlcli db77=> select now() from dual
| EXPRESSION1                |
| -------------------------- |
| 2009-10-10 08:39:17.890000 |

1 row selected (28.149 msec)

regards,

Lars

Edited by: Lars Breddemann on Oct 10, 2009 8:40 AM