Datatype

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
insert the date and time into a SQL database by using hour(now). I am
having a hard time trying to figure out which datatype to use in SQL to
store this value. I have tried using datetime, char, nchar, text and
nothing seems to work. Anyone have any ideas? Thanks!

Regards, :)

Christopher BowenDo you mean that you want to store a time without a date? This isn't
possible in MSSQL, since there is only a single datetime data type.

http://www.aspfaq.com/show.asp?id=2206
http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Use a DATETIME or SMALLDATETIME column.

INSERT INTO YourTable (dt_col) VALUES (CURRENT_TIMESTAMP)

--
David Portas
SQL Server MVP
--|||Christopher,

the function call Hour(Now) will return the current hour, which is an
integer. I would expect this information to be of little use. However,
if you want to store this in an SQL-Server database, then a column of
type tinyint would suffice.

HTH,
Gert-Jan

c_bowen@.earthlink.net wrote:
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen|||It would probably be helpful to see your code and it's not 100% clear what
you're trying to do. I'm guessing hour(now) is a .NET function? Does it
simply return the number of the current hour? That would be some sort of
INT, which would be a datatype mismatch with the datatypes you say you've
tried. Your note mentioned "insert the date and time", which wouldn't
simply be the current hour, anyway.

When I want to insert the date and time, I usually use SQL Server's
getdate() function to supply the value. A T-SQL example would be:

create table foo (col1 char(10),col2 datetime)
go
insert foo values ('abc',getdate())
go
select * from foo
go

[results]
(1 row(s) affected)

col1 col2
---- ----------------
abc 2005-02-25 15:16:38.367

(1 row(s) affected)

Use the datetime datatype if you can. In my experience, using character or
numeric types for storing dates and times usually ends up in grief.

By the way, I usually set things up so that SQL Server is responsible for
supplying the time, rather than the application. That way, if the various
workstation's or web server's clocks are a little bit off, the time values
of rows inserted/updated will still be synchronized across your
applications.

<c_bowen@.earthlink.net> wrote in message
news:1109141117.808195.36560@.l41g2000cwc.googlegro ups.com...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowensql

dataset question

hi,
i am trying to run the following query in the dataset which works fine when
i have a number in the branch_code
SELECT tbl_calendar.date, tbl_day_type.day_type
FROM tbl_calendar INNER JOIN
tbl_day_type ON tbl_calendar.day_type = tbl_day_type.type_id
WHERE (tbl_calendar.date BETWEEN
(SELECT opened_date
FROM
HOBISINT.account_statistics.dbo.t_branches
WHERE branch_code = 56) AND GETDATE())
Problem occurs when i add a branch parameter (int) & make the following change
WHERE branch_code = @.branch)
i get the following error message
Application uses a value of the wrong type for the current operation.
thanks in advanceIs branch_code actually defined as an int in your table?
Can you run (SELECT opened_date FROM
HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
itself without an error?
"Tango" wrote:
> hi,
> i am trying to run the following query in the dataset which works fine when
> i have a number in the branch_code
> SELECT tbl_calendar.date, tbl_day_type.day_type
> FROM tbl_calendar INNER JOIN
> tbl_day_type ON tbl_calendar.day_type => tbl_day_type.type_id
> WHERE (tbl_calendar.date BETWEEN
> (SELECT opened_date
> FROM
> HOBISINT.account_statistics.dbo.t_branches
> WHERE branch_code = 56) AND GETDATE())
> Problem occurs when i add a branch parameter (int) & make the following change
> WHERE branch_code = @.branch)
> i get the following error message
> Application uses a value of the wrong type for the current operation.
> thanks in advance|||Yes it is int
yes i can run below without any errors
thanks for your interest
"daw" wrote:
> Is branch_code actually defined as an int in your table?
> Can you run (SELECT opened_date FROM
> HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
> itself without an error?
> "Tango" wrote:
> > hi,
> > i am trying to run the following query in the dataset which works fine when
> > i have a number in the branch_code
> >
> > SELECT tbl_calendar.date, tbl_day_type.day_type
> > FROM tbl_calendar INNER JOIN
> > tbl_day_type ON tbl_calendar.day_type => > tbl_day_type.type_id
> > WHERE (tbl_calendar.date BETWEEN
> > (SELECT opened_date
> > FROM
> > HOBISINT.account_statistics.dbo.t_branches
> > WHERE branch_code = 56) AND GETDATE())
> >
> > Problem occurs when i add a branch parameter (int) & make the following change
> > WHERE branch_code = @.branch)
> > i get the following error message
> > Application uses a value of the wrong type for the current operation.
> >
> > thanks in advance

Dataset Query using Parameters

OK. I've got a tough one here. I am attempting to create a parameter .aspx
page that will pass in start date, end date and multiple storeIDs to the
report. A section of my query in the report dataset looks like this:
WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
@.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
The problem is at the end with the @.paramStore. It works if you pass it
just one StoreID. The syntax becomes ...StoreID IN ('1')
When you try to pass it more than one storeid, it blows up. The syntax
becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
convert '1,2' to datatype int. Is there a way to take these leading and
trailing apostrophes off or can you think of a workaround? Thanks.You have to do a dynamically generated SQL statement, like this:
= "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
@.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
(" & @.paramStore & "))"
It builds the SQL statement on the fly, so you won't be able to use the
query designer after this. I may not have put the quotes in properly, but I
hope you get the idea.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> OK. I've got a tough one here. I am attempting to create a parameter
> .aspx
> page that will pass in start date, end date and multiple storeIDs to the
> report. A section of my query in the report dataset looks like this:
> WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> The problem is at the end with the @.paramStore. It works if you pass it
> just one StoreID. The syntax becomes ...StoreID IN ('1')
> When you try to pass it more than one storeid, it blows up. The syntax
> becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> convert '1,2' to datatype int. Is there a way to take these leading and
> trailing apostrophes off or can you think of a workaround? Thanks.|||Thanks Jeff but I can't seem to get this to work. It changes my quotation
marks to brackets and comes up with error "Identifier expected."
"Jeff A. Stucker" wrote:
> You have to do a dynamically generated SQL statement, like this:
> = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
> (" & @.paramStore & "))"
> It builds the SQL statement on the fly, so you won't be able to use the
> query designer after this. I may not have put the quotes in properly, but I
> hope you get the idea.
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > OK. I've got a tough one here. I am attempting to create a parameter
> > .aspx
> > page that will pass in start date, end date and multiple storeIDs to the
> > report. A section of my query in the report dataset looks like this:
> >
> > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> >
> > The problem is at the end with the @.paramStore. It works if you pass it
> > just one StoreID. The syntax becomes ...StoreID IN ('1')
> >
> > When you try to pass it more than one storeid, it blows up. The syntax
> > becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> > convert '1,2' to datatype int. Is there a way to take these leading and
> > trailing apostrophes off or can you think of a workaround? Thanks.
>
>|||My advice in this situation is to back up and make sure you can create the
appropriate string.
Create a report that has the report parameters and a textbox and nothing
else. In the textbox put in the expression. Now you should be able to copy
and paste the result into query analyzer and it should work. Sometimes just
seeing the result will let you know what you are doing wrong.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:7236F404-0AB1-44C6-97A1-A601A1E2B738@.microsoft.com...
> Thanks Jeff but I can't seem to get this to work. It changes my quotation
> marks to brackets and comes up with error "Identifier expected."
> "Jeff A. Stucker" wrote:
> > You have to do a dynamically generated SQL statement, like this:
> >
> > = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> > @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID
IN
> > (" & @.paramStore & "))"
> >
> > It builds the SQL statement on the fly, so you won't be able to use the
> > query designer after this. I may not have put the quotes in properly,
but I
> > hope you get the idea.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> > news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > > OK. I've got a tough one here. I am attempting to create a parameter
> > > .aspx
> > > page that will pass in start date, end date and multiple storeIDs to
the
> > > report. A section of my query in the report dataset looks like this:
> > >
> > > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> > >
> > > The problem is at the end with the @.paramStore. It works if you pass
it
> > > just one StoreID. The syntax becomes ...StoreID IN ('1')
> > >
> > > When you try to pass it more than one storeid, it blows up. The
syntax
> > > becomes ...StoreID IN ('1,2') and an error comes up saying that it
cannot
> > > convert '1,2' to datatype int. Is there a way to take these leading
and
> > > trailing apostrophes off or can you think of a workaround? Thanks.
> >
> >
> >

DataSet .xsd use / Re-use concepts?

Hi all,

I've asked this question on another forum on the 2nd October 2006 and has gone unanswered to date.

I'd like to discuss SQL Express DataSet controls use / re-use concepts.

As I have been so busy sorting out the ins-n-outs and the dos-n-dont's of database connections n usage I haven't had a chance to focus on this issue.

Let me exagerate to make my point.

Lets say I have 10 forms.
Each has the standard DataSet, BindingSource, TableAdapter, and BindingNavigator.
Each form has the exact same general (broad scope: select * from myTable, myTable2 etc) query.

My solutions explorer has 1 Dataset .xsd for each forms query:
myDataset01.xsd
myDataset02.xsd
myDataset03.xsd
....
myDataset10.xsd

Is this how I should handle the datasets?
Keeping all dataset/querys seperate. One for each form. And possible multiples for each form?
(In case I want to adjust the number of returned fields on a specific form.)

Or is there a better way (not thru code) to have each form point to the same dataset controls?
i.e. have form2 thru form10 point to the datacontrols on form1? (doesn't sound right now that I've written it.)

Or is there a better way (not thru code) to have each form point to the same Dataset .xsd?
And should I?
i.e. reuse the one Dataset .xsd.

At the moment I'm thinking its all just text any way.
And each dataset has to be reconnected and data retrieved from the database anyway.

So does it really matter?

As I recall, I was having so many crashes when setting up the dataset .xsd that I found it was safer to have just one dataset per query.

I've ordered VS 2005 Pro and hope its more stable.

--

I've also found that if I update the SQL Express Table thru Management Studio
(and I believe thru the database explorer in the vb.net IDE) that the .xsd file gets lost.

i.e. I have to go thru and duplicate the changes.

Is there an easier way?

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

hmmm, I'm wondering if its the way I've written this query.

Or could it be I'm so off the mark that it would take too long to answer?

Can anyone out there please confirm that they:

1) Understand this query
2) Don't know the answer
3) Would also like to know the answer

|||

I've just had a quick look at another developers DataSet.

There is only one dataset.

It contains all the Tables in the database.

In the dataset designer there are additional queries at the bottom of each table.

I wonder how you access these additional queries thru code?

|||

Jeez! LOL! Now I'm rolling...

Thru the dataset designer you can right click on the table and select "Add new querie"
The wizard will step you thru then ask you to name your query and give you a starting value of "Fillby"
You need to add text to FillBy like FillByCity

Dim cityValue As String = "Seattle"
CustomersTableAdapter.FillByCity(NorthwindDataSet.Customers, cityValue)

http://msdn2.microsoft.com/en-us/library/kda44dwy(VS.80).aspx

Data-driven subscription: put parameter in the filename text

Hi all,

I've developed a data driven subscription report. I have a paramete (named "Data") that is a result of my query to the current date. It is working fine.

Now I would like to make one change: In the step4 of the creation of the data-driven report we have the option to give a name to the filename.The name that I gave was TestFile. In this option i'm also able to select the parameter instead of giving the name to the filename. Can I make something like TestFile_ & @.Data? Wich would result in TestFile_01-02-2007? Or the only way is to make, in the query of the paramenter another field with this result?

Thanks in advance.

Marco.

I tried the other option, making it by a query and this is what I want.

Anyway, instead of making this in my query, I couldn't have done this in the text? Like I said TestFile_ & @.Data? This would be interpreted as a string am I right?

Data-driven Subscription with Date Parameter from DB

I'm not having fun anymore... I have done this in the past and now I am
unable to get this to work.
I have a report that accepts a date as a parameter. When called through the
web-page or in a subscription with static values for the date, this works. It
also works if the default value for the parameter is used. The default value
is a call to a datasource that simply calls "SELECT GETDATE()".
When I set up a data-driven subscription, give it the same datasource, give
the query of "SELECT GETDATE() AS REPORTDATE", and set the parameter to use
the REPORTDATE value; an error occurs stating that a type mismatch has been
encountered.
I have created a test report (attached below) that exhibits the same issue.
Set up a data-driven subscription with the "SELECT GETDATE() AS REPORTDATE"
query on any datasource.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.25in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.6875in</Width>
<CanGrow>true</CanGrow>
<Value>=Parameters!STARTDATE.Value</Value>
<Left>1.625in</Left>
</Textbox>
</ReportItems>
<Style />
<Height>0.375in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="CPR_commerce">
<rd:DataSourceID>ec9dccfe-38c9-45ca-a0c0-7364bfc665e8</rd:DataSourceID>
<DataSourceReference>CPR_commerce</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DEFAULTDATE">
<Fields>
<Field Name="ID">
<DataField />
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>CPR_commerce</DataSourceName>
<CommandText>SELECT GETDATE()</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>2549c4c5-9946-4a66-abda-7784310a7b58</rd:ReportID>
<PageFooter>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.25in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="STARTDATE">
<DataType>DateTime</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DEFAULTDATE</DataSetName>
<ValueField>ID</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>STARTDATE</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>Well... I've solved it. It seems that the locale setting was set to en-CA and
it was messing up the times. Not sure why or how this got set to that but I'm
sure I'll be able to figure it out or avoid it in the future... man, what a
pain.
"Rixmann" wrote:
> I'm not having fun anymore... I have done this in the past and now I am
> unable to get this to work.
> I have a report that accepts a date as a parameter. When called through the
> web-page or in a subscription with static values for the date, this works. It
> also works if the default value for the parameter is used. The default value
> is a call to a datasource that simply calls "SELECT GETDATE()".
> When I set up a data-driven subscription, give it the same datasource, give
> the query of "SELECT GETDATE() AS REPORTDATE", and set the parameter to use
> the REPORTDATE value; an error occurs stating that a type mismatch has been
> encountered.
> I have created a test report (attached below) that exhibits the same issue.
> Set up a data-driven subscription with the "SELECT GETDATE() AS REPORTDATE"
> query on any datasource.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <PageHeader>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage>
> <Style />
> <Height>0.25in</Height>
> </PageHeader>
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>2.6875in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Parameters!STARTDATE.Value</Value>
> <Left>1.625in</Left>
> </Textbox>
> </ReportItems>
> <Style />
> <Height>0.375in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="CPR_commerce">
> <rd:DataSourceID>ec9dccfe-38c9-45ca-a0c0-7364bfc665e8</rd:DataSourceID>
> <DataSourceReference>CPR_commerce</DataSourceReference>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="DEFAULTDATE">
> <Fields>
> <Field Name="ID">
> <DataField />
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>CPR_commerce</DataSourceName>
> <CommandText>SELECT GETDATE()</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>2549c4c5-9946-4a66-abda-7784310a7b58</rd:ReportID>
> <PageFooter>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage>
> <Style />
> <Height>0.25in</Height>
> </PageFooter>
> <BottomMargin>1in</BottomMargin>
> <ReportParameters>
> <ReportParameter Name="STARTDATE">
> <DataType>DateTime</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>DEFAULTDATE</DataSetName>
> <ValueField>ID</ValueField>
> </DataSetReference>
> </DefaultValue>
> <AllowBlank>true</AllowBlank>
> <Prompt>STARTDATE</Prompt>
> </ReportParameter>
> </ReportParameters>
> <Language>en-US</Language>
> </Report>

Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors