If you’re getting an error that reads “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” in SQL Server, it’s probably because you’re trying to change the data type of a sequence object.
We can’t actually change the data type of a sequence object, so this error is to be expected.
If you need to change the data type of a sequence object, you’ll need to drop the sequence and recreate it with the correct data type.
Continue readingIn SQL Server, the FOR XML clause allows us to return the results of a query as an XML document.
When we use this clause, we have various options for how we want to shape the resulting XML document.
Below is an explanation of how the FOR XML clause works, including the basic options we have when using it.
SQL Server’s ISDATE() function checks whether or not an expression is a valid date. However, you may be aware that this function doesn’t work on datetime2 values. On datetime2 values it returns 0, which means it’s not a valid date, even when the value is a valid date.
This is obviously not ideal, because the datetime2 type is a valid date type. Microsoft even recommends that we use datetime2 instead of datetime for our dates, as it aligns with the SQL Standard and it provides more fractional seconds precision.
Anyway, below are three options we can use to check whether a datetime2 value is a valid date.
Continue readingWhen we create a sequence in SQL Server, we have the option of making it an incrementing sequence or decrementing.
By “decrementing”, I mean that the sequence decreases instead of increases. For example, if it starts at 100, the next value is 99, and then 98, and so on.
To create a sequence that decrements, all we do is provide a negative value for the INCREMENT BY argument.
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 readingIf 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.
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.
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 readingIf 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 readingIf you’re getting an error that reads something like “START value (11) cannot be greater than MAXVALUE (10)” in PostgreSQL when you’re trying to create a sequence, it’s because your sequence’s start value is higher than its maximum value, when it should be lower or the same.
To fix this issue, be sure that the sequence’s maximum value is not less than its start value.
Continue reading