finding bad date

I have a database where a varchar field is being used to store dates. I wrot
e
the following function to check if the data is a valid date. If it is not a
valid date, Null is returned.
ALTER function f_ToDate(@.inDate varchar(50))
returns datetime
begin
declare @.returndate datetime
if isdate(@.inDate) = 0
select @.returndate = null
else
select @.returndate = convert(datetime,@.inDate,101)
return @.returndate
end
In my database, I have a table called Generation with a field called
CreateDate. When I run the following query, I get an error message stating
that “syntax error converting datetime from character string”
select dbo.f_ToDate(Generation.CreationDate) from Generation
To check, I ran the following query and it returned all null records which
is expected
select CreationDate from general where isdate(CreationDate) = 0
The following returns no records which is expected.
select CreationDate from general where isdate(CreationDate) = 0 and
CreationDate is not Null.
I can’t find the record that is causing “select
dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.I have had a similar issue w/ Dates. A quick and dirty way that I used was t
o
export out the records using DTS to a table that mirrored the working one.
Only change I made was to change the varchar date field to a true datetime
field in the temp table. In my dts package I set the max number of errors to
the max (999) and started the export.
In my case I had 632 fewer records in my temp table after the export. I ran
a NOT EXISTS against the tables and was able to find the 632 records w/ bad
dates in my production table.
If the number of records w/ bad dates is GT 999, you will have to do the
export in bacthes to find all the bad recs.
Just a thought!
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Emma,
The convert function with style 101 is supposed to be used when converting
from datetime to char or varchar. Can you execute:
select case when isdate(CreationDate) = 0 then null else convert(datetime,
CreationDate)
from Generation
AMB
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Thanks Jay for your response. I did just what you said. The number of record
s
exported to the temp table was the same as in my original table, and the
query work on the temp table and not on the original. I can’t figure it ou
t.
"Jay Kusch" wrote:
> I have had a similar issue w/ Dates. A quick and dirty way that I used was
to
> export out the records using DTS to a table that mirrored the working one.
> Only change I made was to change the varchar date field to a true datetime
> field in the temp table. In my dts package I set the max number of errors
to
> the max (999) and started the export.
> In my case I had 632 fewer records in my temp table after the export. I ra
n
> a NOT EXISTS against the tables and was able to find the 632 records w/ ba
d
> dates in my production table.
> If the number of records w/ bad dates is GT 999, you will have to do the
> export in bacthes to find all the bad recs.
> Just a thought!
> "Emma" wrote:
>|||Thanks. That worked.
"Alejandro Mesa" wrote:
> Emma,
> The convert function with style 101 is supposed to be used when converting
> from datetime to char or varchar. Can you execute:
> select case when isdate(CreationDate) = 0 then null else convert(datetime,
> CreationDate)
> from Generation
>
> AMB
> "Emma" wrote:
>

find out active Sql statements

Hi,
Where does Sql server store the most current active T-SQL
statements? Is there a system table or stored procedure(documented or
undocumented)that will show you the active sql statements being
executed. For eg: in oracle you can query v$sql to see all the sql
statements executed by each session.
I know that you can use Enterprise manager, but was wondering if you
can run a query via query analyzer to look at it.
Any help is appreciated.

Thanks
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1106264397.579113.143730@.c13g2000cwb.googlegr oups.com...
> Hi,
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.
> Any help is appreciated.
> Thanks
> Geetha

Check out fn_get_sql() in Books Online - if it isn't there, you'll need to
download the latest BOL version from Microsoft:

http://www.microsoft.com/sql/techin.../2000/books.asp

DBCC INPUTBUFFER is another option, but it can only display the first 255
characters of whatever batch is being executed.

Simon|||[posted and mailed]

(gdabbara@.brownshoe.com) writes:
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.

Simon pointed you to the basics. To get it all nicely packaged, I
have stored procedure aba_lockinfo that will give you the information.
Have a look at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks you all for the information. It was very helpful.

financial year

how can you store a financial year in sql 2005

wt do you mean by that? can u explain it further so that we can get the exact idea wt u wanna do?|||

What i ment is that when a person selects a date how do you set another column just to show the financial year....

EG:

column 1 = '09/09/2004'

column 2(financial Year) = '2005'

what i have done is that i have created a case satement that selects the correct date

THANKS

|||

You can do it in a select statement:

SELECT tDate, CASE WHEN MONTH(tDate)>6 then YEAR(tDate)+1

ELSE YEAR(tDate) END AS FYear

FROM Dates

Or you can save the value in a new column such as a INT column through an update statement:

UPDATE Dates

SET fYear = CASE WHEN MONTH(tDate)>6 then YEAR(tDate)+1

ELSE YEAR(tDate) END

|||

I would absolutely suggest a calendar table like this for your translation needs. You calculate it once, store it away and then reference it. Here is an article (with code) I wrote on how to load a calendar table, including columns for fiscal_year and month:

A way to load a calendar table
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

It also has several references to other articles.

financial year

how can you store a financial year in sql 2005

wt do you mean by that? can u explain it further so that we can get the exact idea wt u wanna do?|||

What i ment is that when a person selects a date how do you set another column just to show the financial year....

EG:

column 1 = '09/09/2004'

column 2(financial Year) = '2005'

what i have done is that i have created a case satement that selects the correct date

THANKS

|||

You can do it in a select statement:

SELECT tDate, CASE WHEN MONTH(tDate)>6 then YEAR(tDate)+1

ELSE YEAR(tDate) END AS FYear

FROM Dates

Or you can save the value in a new column such as a INT column through an update statement:

UPDATE Dates

SET fYear = CASE WHEN MONTH(tDate)>6 then YEAR(tDate)+1

ELSE YEAR(tDate) END

|||

I would absolutely suggest a calendar table like this for your translation needs. You calculate it once, store it away and then reference it. Here is an article (with code) I wrote on how to load a calendar table, including columns for fiscal_year and month:

A way to load a calendar table
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

It also has several references to other articles.