Category: DBMS

Database Management Systems

Fix Error Msg 235 “Cannot convert a char value to money. The char value has incorrect syntax” in SQL Server

Posted on by Ian

If you’re getting error msg 235 which reads Cannot convert a char value to money. The char value has incorrect syntax, it’s probably because you’re trying to convert a string to the money data type, but the string isn’t in a format that can be converted to the money type.

To fix this issue, make sure you’re trying to convert the right value (perhaps you’ve got the wrong column or variable). If you’re sure you’re trying to convert the right value, try a workaround like the one below.

Continue reading →

How CURRVAL() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the currval() function returns the value most recently returned by nextval() for the specified sequence in the current session.

The currval() function is very similar to the lastval() function, except that lastval() doesn’t require the name of a sequence like currval() does. That’s because lastval() doesn’t report on any particular sequence – it reports on the last time nextval() was used in the current session, regardless of which sequence was used. The currval() on the other hand, only reports on the specified sequence.

Continue reading →

How LASTVAL() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the lastval() function returns the value most recently returned by nextval() in the current session.

The lastval() function is very similar to the currval() function, except that lastval() doesn’t require the name of a sequence like currval() does. That’s because lastval() doesn’t report on any particular sequence – it reports on the last time nextval() was used in the current session, regardless of which sequence was used.

Continue reading →

Fix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZINTERSTORE in Redis

Posted on by Ian

If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZINTERSTORE command in Redis, it’s probably because you’re passing a key with the wrong data type.

To fix this issue, be sure that each key you pass to the ZINTERSTORE command is either a set or a sorted set.

Continue reading →

How to Change the Range of a Sequence in SQL Server

Posted on by Ian

SQL Server sequence objects allow us to increment or decrement through a range of numbers. Once set, we don’t need to change anything. We can simply use NEXT VALUE FOR to generated the next sequential number.

But what if we want to change the sequence to use a different range?

For example, we created a sequence that increments between 0 and 100, but now we want to change it to increment between 200 and 300.

Easy. We can use the ALTER SEQUENCE statement to do just that.

Continue reading →

Get the Number of Rows Affected by Previous SQL Statement

Posted on by Ian

Some RDBMSs provide an easy way for us to find out how many rows were affected by the last SQL statement. This can be handy when running INSERT, UPDATE, or DELETE statements.

The method used depends on the DBMS we’re using. Below, I look at how some of the major DBMSs implement this functionality.

Continue reading →

MySQL BENCHMARK() Explained

Posted on by Ian

In MySQL, BENCHMARK() is a built-in function that executes an expression repeatedly for a specified number of times.

It can be used to time how quickly MySQL processes the expression. Specifically, the function is intended for measuring the runtime performance of scalar expressions.

The result is always 0, or NULL for inappropriate arguments. The function is intended to be used within the mysql command line tool, which reports query execution times.

Continue reading →

Fix Msg 529 “Explicit conversion from data type date to int is not allowed” in SQL Server

Posted on by Ian

If you’re getting SQL Server error Msg 529 that reads Explicit conversion from data type date to int is not allowed, it’s because you’re trying to explicitly convert a date data type to an int data type, which is not allowed in SQL Server.

To fix this issue, try converting the date value to a string first, and then to an integer.

Alternatively, change the destination type to one that’s allowed.

Also, check that you’re trying to convert the correct value. For example, you may have selected the wrong column or variable. In this case, selecting the correct column may fix the problem.

Continue reading →