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


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

For your request: sorted by alphabets and then by numeric values I am assuming (deriving from your sample data), that only the first letter should be treated as text to sort by (easy to adapt). Further assuming that you want to sort by the first number in the string next (digits only). To break ties with trailing characters I finally order by the ...


3

Personally, I would buy the odbc driver and import all the files with the SQL wizard http://www.softvelocity.com/drivers/tsodbc.htm


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

Tough task. I would assume the worst and create: strings(x) as nvachar(x) Long as BigInt Dec(x,x) as Dec(x,x) After that you'll have to look at the data in each column individually and make decisions on your findings. Dates might be stored as strings. (MS is guilty of this as well in TypePerf.)


2

There is a way. Given a table t and a function f() that returns an anonymous record that would match that table type: CREATE TABLE t (id int, d date); You cannot just cast the anonymous record, since a column definition list is required for SELECT * FROM f() Quoting the manual on the SELECT command: If the function has been defined as returning ...


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