You should try a CROSS JOIN.

The problem is that you're getting the month for the "current" date instead of subtracting the week as you do in your convert.

Check this out:

SELECT GivenDate,

...

The problem is with your way to handle errors. You need to have a try-catch block for each statement.

I guess that you have something like this:

BEGIN TRY

...

Do you have a try...catch block for each table/file?

Can you post what have you tried?

Why would you use string manipulation when it's easier to work directly with the numbers using some math?

Here's a modification of Eirikur's code.

USE tempdb;

GO

DECLARE @SAMPLE_SIZE INT ...

What do you mean by SQL compatible? SQL Server can't generate 100% SQL code. And no RDBMS can use it, either. SQL Server will generate T-SQL code which is the...

This is an example on how to do it through dynamic code.

DECLARE @divisor int = 80,

@dividend int = 10,

@table varchar(128)...

You can't do this. To be able to query different tables and columns, you need to use dynamic code. Dynamic code can be dangerous if it's not handled correctly and...

Hi scantoria,

I'm glad that you read the questions. They weren't intended specifically for you, they're part of my signature and appear on all my posts. You wouldn't believe the amount...

You could do something like this:

SELECT 'TableName1' AS ColumnA,

SomeColumn AS ColumnB

FROM TableName1

UNION ALL

SELECT 'TableName2' AS ColumnA,

SomeColumn AS ColumnB

FROM TableName2

You just add a string literal as the column.

SELECTc.cust_fullname AS Name,

c.cust_membership_id AS Account,

t.c_amount AS Amount,

'CH' + CAST(t.i_ticket_id AS varchar(12)) AS Doc_Num,

t.s_credit_tran_type AS Detail_Account,

...

No one would be capable of give any help with such little information. You're asking for a DB design but that can be a really big project and you might...

Could you post DDL for tables and sample data that will replicate the issue?

Table and column names can be changed if needed.

Are you sure that's the problem?

If I run the following, it gives the correct result:

SELECT TBI.CurrDateTime,

CAST((LEFT(TBI.CurrDateTime, 10) + ' ' + RIGHT(RTRIM(LTRIM(TBI.CurrDateTime)), 15)) AS DATETIME2(6)),

...

This was fun, but I'm not sure I have the complete solution. The difficult part is done without dynamic sql.

By the way, this seems like a really bad design, but...