Tag Info

Hot answers tagged

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

From sql_variant (Transact-SQL) When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values. The base data type family for @v is Exact numeric and the base data type ...


4

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


3

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


3

I'd tend to export each table as CSV from Access, something it's quite good at. Then import it into PostgreSQL using COPY, possibly via a preprocessor like a Python script with psycopg2 if there's lots of mangled / invalid data. If there are lots of tables, VBA (Visual Basic for Applications) or similar can be used on the Access side to automate the export, ...


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

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

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


1

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


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

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :) Don't really see any solutions here except updating the SQL query. This ...



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