BCP/Bulk Insert Datetime Problem,

Hi,

I am having problems getting BCP/Bulk Insert to insert dates properly.

The dates are being presented to us as,

dd/MM/yyyy hh:mm:ss

however, when importing into SQL Server as a datetime field via bcp/bulk insert, they are being inserted as

MM/dd/yyyy hh:mm:ss

In Australia, Windows region date setting set correctly and using Latin1_General_CI_AS collation.

Using the following format file (just the relevant sample from the file),

8.0
9
1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS

I tried using

1 SQLDATETIME 0 17 "," 1 startDateTime ""

however this did not work.

Please let me know if any additional info needed.

Thanks!Dates are stored internally as numbers...the rest is just a presentation issue...look up convert in BOLsql

BCP / Bulk Insert Date Problems,

Hi,
I am having problems getting BCP/Bulk Insert to insert
dates properly.
The dates are being presented to us as,
dd/MM/yyyy hh:mm:ss
however, when importing into SQL Server as a datetime
field via bcp/bulk insert, they are being inserted as
MM/dd/yyyy hh:mm:ss
In Australia, Windows region date setting set correctly
and using Latin1_General_CI_AS collation.
Using the following format file (just the relevant sample
from the file),
8.0
9
1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS
I tried using
1 SQLDATETIME 0 17 "," 1 startDateTime ""
however this did not work.
Please let me know if any additional info needed.To circumvent the problem load all data into a Holding Table, load the date
data into a varchar column. Then INSERT the data into it's destination with
a select statement that CONVERTs the varchar dates into datetime. Specify
the additional STYLE parameter in your CONVERT to ensure you capture the
dates correctly, i beleive dd/mm/yyyy is STYLE 103.
HTH
Ryan Waight, MCDBA, MCSE
"Mallen" <mallen@.nospam.bigpond.net.au> wrote in message
news:04ef01c3a981$07cd3470$a501280a@.phx.gbl...
> Hi,
> I am having problems getting BCP/Bulk Insert to insert
> dates properly.
> The dates are being presented to us as,
> dd/MM/yyyy hh:mm:ss
> however, when importing into SQL Server as a datetime
> field via bcp/bulk insert, they are being inserted as
> MM/dd/yyyy hh:mm:ss
> In Australia, Windows region date setting set correctly
> and using Latin1_General_CI_AS collation.
> Using the following format file (just the relevant sample
> from the file),
> 8.0
> 9
> 1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS
> I tried using
> 1 SQLDATETIME 0 17 "," 1 startDateTime ""
> however this did not work.
> Please let me know if any additional info needed.

Basic SQL: Multiple AND/OR nightmare in SELECT statement

This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...

I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.

SELECT *
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
ParentID1 = COALESCE(@.taxparent, ParentID3) OR
ParentID2 = COALESCE(@.taxparent, ParentID2) OR
ParentID3 = COALESCE(@.taxparent, ParentID3) OR
ParentID4 = COALESCE(@.taxparent, ParentID4) OR
ParentID5 = COALESCE(@.taxparent, ParentID5) OR
ParentID6 = COALESCE(@.taxparent, ParentID6) OR
ParentID7 = COALESCE(@.taxparent, ParentID7) OR
ParentID8 = COALESCE(@.taxparent, ParentID8)

The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.

Has anyone got a good tip to help me resolve this puzzle?

Hi

You can also use IN and NOT IN as well as Having caluse to verify condition. As ( and ) paranthesis can also help you to verify condition on a condition,

|||Hi Akbar,

Sorry, but that is not really helpful.

As far as I understood, IN is used to test multiple values against a single column. I am testing a single value against multiple columns.

HAVING is used with aggregate values. I am not using those.

As I already wrote, using parenthesis does not work for me, or I do not know how to properly apply them in this particular case.

I am still with my hands in my hair on finding a solution to this, so I would appreciate any help.|||All right, fair enough, after some study, I was able to simplify the code using 'IN', that -new to me- could also be used for testing a single value against multiple columns. But I am stuck with the same problem that it won't combine with the rest of the conditions in order to yield the desired results!

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' OR
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me too many results and

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me no results....

|||OK, another discovery! When I comment out:

ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%'

It does actually succesfully combine the different criteria!

This must mean something goes wrong with those lines only...

Here is the entire code of the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [petrander].[DynamicQuery]
@.taxparent int = NULL,
@.museum int = NULL,
@.collection int = NULL,
@.binomen Nvarchar(254) = NULL,
@.locality Nvarchar(254) = NULL
AS
SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE 'N%' + @.binomen + '%' AND
Locality LIKE 'N%' + @.locality + '%' AND
@.taxparent IN (ParentID1,
ParentID2,
ParentID3,
ParentID4,
ParentID5,
ParentID6,
ParentID7,
ParentID8)

Could the problem lie in combining null values with the LIKE 'N%' + statements?|||Problem lay somewhere else and solution can be seen in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620363&SiteID=1