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 ...


7

Data Flow Here's the general approach I'd take to solving your problem. I started with your source data and added some other conditions - a NULL as well as a 14 and 15 year to ensure my logic later is correct. SELECT D.DrvDOB FROM ( VALUES ('470324') , ('470324') , ('470209') , ('140209') , ('150209') , ('101') , ('0') , ...


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

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

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 ...


3

Why don't you just use?: WHERE DateTime >= '2014-01-01' AND DateTime < CONVERT(CHAR(19), DATEADD(second, 1, GETDATE()), 126) Advantages: - No values in the varchar column will be converted so you'll get no errors - Efficiency as indexes can be used Disadvantages: - does not take care of timezone offsets - some of the values, while being ...


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'}; ...


2

Try type casting to datetimeoffset type as below: CAST(datetime as DATETIMEOFFSET) For instance your query will become something like following: SELECT * FROM dbo.RebroadcastSmoothStreaming WHERE (CAST(datetime as DATETIMEOFFSET) BETWEEN CAST('2014-01-01T00:00:00-06:00' AS datetimeoffset) AND dateadd(day, 1, SYSDATETIMEOFFSET())) ORDER BY Ip


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 ...


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. ...


1

The issue is that you cannot CONVERT or CAST a VARCHAR ISO8601 datetime with an offset to a DATETIME. From SQL Server 2008 onwards, the DATETIMEOFFSET datatype was introduced to handle datetimes with offsets. As answered elsewhere, you would need to CAST your DateTime VARCHAR column to a DATETIMEOFFSET SELECT * FROM dbo.RebroadcastSmoothStreaming WHERE ...


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 - ...



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