Tag Info

Hot answers tagged

11

In SQL Server 2008, converting binary to a character representation became a lot faster and easier: CREATE TABLE dbo.X ( pk integer PRIMARY KEY, c1 integer NOT NULL, rv rowversion NOT NULL, rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1) ); Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to ...


5

This type of formatting is generally best done in your application if possible. The problem is that the case expression returns a result based on the highest datatype precedence of any branch. So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar. Also you should probably add year into your ...


5

Since BETWEEN is very problematic due to rounding of different date/time types and other problems, and since YYYY-MM-DD is not a safe format without the awkward T, an open-ended range using ISO standard full dates with no separators is a much better approach: WHERE Created >= '20141101' AND Created < '20141201';


4

It is ugly, but you can try: SELECT a, b::text FROM unnest(ARRAY[(1,'hello'), (3,'world')]) AS t(a integer, b unknown); This way the type defined in AS matches the output of unnest(), which you can cast to your needs in the SELECT list. You can try this in a small SQLFiddle.


4

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. ...


3

You can do this without generating a warning by creating a type and casting the records to it: create type t as (a integer, b varchar(255)); select * from unnest(array[(1,'hello'), (3,'world')]::t[]); ┌───┬───────┐ │ a │ b │ ├───┼───────┤ │ 1 │ hello │ │ 3 │ world │ └───┴───────┘ tested on 9.4 and 9.3 (SQLFiddle here)


3

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format. Try this: select count(*) from dbo.profile where [created] between ...


2

To convert an hexadecimal string to characters, the encoding must be specified. This is necessary because the same series of bytes may produce different characters depending on their encoding. For instance, if the hex codes represent an utf-8 string, use: select convert_from(decode(your_hex_string, 'hex'), 'utf-8'); If the encoding is not supported by ...


2

Another alternative, I recommend using ODBC datetime literals. Despite their name they do not require you connect via ODBC. They bypass the usual conversion rules in SQL Server and are always interpreted as a datetime. SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN {TS '2014-11-01 00:00:00.000'} AND {TS '2014-11-30 23:59:59.997'}; ...


1

in 5.6.12: (Well, not really an answer, but more info.) mysql> insert into se108833 (product_id,product_id_nn) values ('',''); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message ...


1

As you mention you string value format is in 21/11/2105 then you can try following one Alter table table name alter column column name datetime NULL ( null is optional if you want to allow null value to your field). Note that if the column contain string value like "abc" (other than date format value) then it will show error as conversion fail.


1

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar). In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE ...


1

Should do it: SELECT a, b FROM unnest(ARRAY[(1,varchar 'hello'), (3,varchar 'world')]) AS t(a integer, b varchar(255));


1

Dates in Clarion TPS files are stored as a Clarion Long, which is an integer. They are defined as the # of days elapsed since December 28, 1800. The valid Clarion Standard Date range is January 1, 1801 through December 31, 9999 February 27, 2015 - the date Nimoy died - is Clarion date 78,223. A Clarion String is a CHAR. A Clarion Cstring is a VARCHAR - ...


1

You need the USING clause to ALTER TABLE ... TYPE ..., e.g.: ALTER TABLE mytable ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11) USING ("Longditude"::NUMERIC(14,11)), ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11) USING ("Latitude"::NUMERIC(14,11)); assuming you want a numeric with precision 14 and scale 11 and your columns really do have an ...


1

The first thing I would do is identify rows where those 6 characters can't possibly be an integer. SELECT ProcessInstanceAppianID, ProcessInstanceDescription FROM InternalUseOnly.dbo.processinstance WHERE RIGHT(ProcessInstanceDescription, 6) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'; Then fix that data or somehow exclude it from the results. ...



Only top voted, non community-wiki answers of a minimum length are eligible