DataAdapter - SELECT Statement - items in last 30 days

I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.

I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.

What is the WHERE clause I sholud use to do this??

ThanksTry with the following SQL statement, i belive it should work.

select * from <tablename> where datediff(day, <columnname>, getdate()) < 30

Hope it solves your issue.|||Thanks very much, it worked a treat

Data types question - Varbinary and timestamps

Greetings once again SSIS friends,

I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).

I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.

Please advise on what data type is suitable.

Thanks for your help in advance.

Greetings once again guys,

I found the following article which gives a workable solution to my problem, but I am still interested to find out the equivalent of timestamp data type in SSIS.

http://solidqualitylearning.com/blogs/erik/archive/2005/12/09/1499.aspx

|||

DT_BYTES

binary, varbinary, timestamp

binary, varbinary, timestamp

BigBinary, VarBinary

RAW

RAW

You will want to use DT_BYTES. See this article on MSDN.

http://msdn2.microsoft.com/en-us/library/ms141036.aspx

Does this answer your question?

Data type: Text vs VarChar (10000)

I have a table that is used to archive communications.
One of the fields is ComText, I've used the "Text" data type for this field.
Now my question is if I use a varchar data type with the length of (10000) would it improve performance in any way (specialy for reporting purposes)?The maximum row size is less than 8k. Plus, it is not good to have a very long varchar field, the space usage will be poor.|||Thanks for your reply.
Do you have experience regarding this issue and an Access front end, cause it seems to me that access reports have some trobule printing reports with memo fields, specially when the machine has a low RAM.

Data type problem

Greetings SSIS friends,

I cast one of my fields from a decimal to an int in my data source (generated from a SQL command). But when I try and map that field to another one of my tables, i get an error saying that I can not cvonvert from decimal to Int32.

Is there any solution which doest require using the derived column or data conversion components?!

Your help would be appreciated.

Can you provide your SQL so that we can look at it? If you are casting to an integer from a decimal in the SQL, SSIS should only see an integer...|||

Hi Phil,

That's what I don't understand.

The SQL is pretty straightforward to be honest.

select
distinct
b.eventid as evt_eventid,
b.meetid as evt_meetid,
cast(h.longname as varchar) as evt_long_name,
cast(h.shortname as varchar) as evt_source_short_name,
b.expstartdate as evt_expstartdate,
b.expstarttime as evt_expstarttime,
b.eventnumber as evt_eventnumber,
b.expertgroup as evt_expertgroup,
b.currentrun as evt_currentrun,
b.enddate as evt_enddate,
b.endtime as evt_endtime,
b.eventstatus as evt_eventstatus,
f.etypeid evt_etypeid,
cast(g.longname as varchar) as evt_type_long_name,
cast(h.longname as varchar) as evt_name,
b.maxplaces as evt_maxplaces,
b.maxpayout as evt_specific_maxpayout,
f.maxpayout as evt_default_type_maxpayout,
b.offdate as evt_offdate,
b.offtime as evt_offtime,
cast(b.callitap as char(1)) as evt_callitap,
cast(isnull(b.maxstake, 0) as int) as evt_maxstake,
b.scoop6leg as evt_scoop6leg,
b.riskoff as evt_riskoff,
b.userofftime as evt_userofftime,
b.delayofftime as evt_delayofftime,
cast(b.suspendliability as int) as evt_suspendliability,
b.termsid as evt_specific_termsid,
i.termsid as evt_default_termsid,
cast(j.longname as varchar) as evt_default_terms,
cast(l.longname as varchar) as specific_evt_terms,
convert(varchar(20), convert(bigint, b.updatetimestamp)) as evt_updatetimestamp
from meetings as a
inner join events as b
on a.meetid = b.meetid
inner join names as c
on a.nameid = c.nameid
inner join EvenType as d
on a.etypeid = d.etypeid
inner join names as e
on d.nameid = e.nameid
inner join EvenType as f
on b.etypeid = f.etypeid
inner join names as g
on f.nameid = g.nameid
inner join names as h
on b.nameid = h.nameid
inner join terms as i
on f.termsid = i.termsid
inner join names as j
on i.nameid = j.nameid
left join terms as k
on b.termsid = k.termsid
left join names as l
on k.nameid = l.nameid
where
convert(varchar(20), convert(bigint, a.updatetimestamp)) > ?
or convert(varchar(20), convert(bigint, b.updatetimestamp)) > ?

order by b.meetid, b.eventid

In SSIS however, it still thinks the column is a decimal.

|||Did you previously have the decimal coming into the source and then later changed the SQL to cast it to an integer?

In the source column mappings, delete the row that corresponds to the column you are working with, and re-map it. Does that help?|||

Hi Phil,

Yes you are right. I unmapped the column and then remaped it and reconfigured my other merge joins and it is now in the data type that I cast it in the SQL code.

Thanks for your help.

Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regards
Hi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards
|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2) as
int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2) as
int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:

> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards
|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2) as
> int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2) as
> int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:

Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regardsHi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2
) as
int)*60*60+cast(substring('34:15:10',4,2
) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2
) as
int)*60*60+cast(substring('24:18:10',4,2
) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:

> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,
2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,
2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2
) as
> int)*60*60+cast(substring('34:15:10',4,2
) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2
) as
> int)*60*60+cast(substring('24:18:10',4,2
) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:
>

Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regardsHi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2) as
int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2) as
int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2) as
> int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2) as
> int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:
> > Hello,
> >
> > I need to convert one character field to datetime and after make the
> > differenc between two fields.
> > The problem is that if in the output result appears one value that is
> > biggest then 23:59:59 hours its shown the
> > following error:
> >
> > (xxxxxxx row(s) affected)
> > Server: Msg 242, Level 16, state 3, line 1
> > The convertion of a char data type to a datetime data type resulted in an
> > out-of-range datetime value
> >
> > Example:
> >
> > select convert(datetime, field, 108) from table_name
> > or
> > select convert (datetime, '24:00:00', 108) from table_name
> >
> >
> > (xxxxxxx row(s) affected)
> > Server: Msg 242, Level 16, state 3, line 1
> > The convertion of a char data type to a datetime data type resulted in an
> > out-of-range datetime value
> >
> > I need to use all the results and know the difference between them in hour
> > or minutes.
> > Example:
> >
> > select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> > field_B, 108))
> > from table_name
> >
> > Can you help me?
> >
> > Thanks and best regards

Data type conversion problem

Hi,
I have a variety of fields that I am trying to convert from Float to Decimal
for greater accuracy however we are having some problems converting them.
We use this fantastic tool called "SQL compare" by Red-Gate software except
that it's failing in one spot. So, we are now working with the remaining
fields that need to be converted field-by-field.
When we try to convert the field called dpst_amt it fails with:
Msg 8115, level 16, State 6, line 1
Arithmetic overflow error converting float to data type numeric.
Statment terminated
The values in [dpst_amt] field are numbers like 693.399999999998, if I try
[select round(dpst_amt,2)] the value never is rounded, if I try to copy the
value out to X field it fails as well. How did I determine what is in these
fields, meaning how do I find the bad record? One must be bad but where or
how do I find it?
Thank you!
ChrisWhat is the size of your decimal datatype? There must be some data in your
column that is larger than the largest that fits in the decimal datatype, so
if your decimal is for example decimal(6,2)
SELECT dpst_amt
FROM your_table
WHERE ABS(dpst_amt) > 9999.99
should give you the values that are outside the range of your decimal.
SELECT MAX(ABS(dps_amt)) FROM your_table
will give you an indication as to what the size of your decimal datatype
should be.
Jacco Schalkwijk
SQL Server MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
> Hi,
> I have a variety of fields that I am trying to convert from Float to
> Decimal for greater accuracy however we are having some problems
> converting them. We use this fantastic tool called "SQL compare" by
> Red-Gate software except that it's failing in one spot. So, we are now
> working with the remaining fields that need to be converted
> field-by-field.
> When we try to convert the field called dpst_amt it fails with:
> Msg 8115, level 16, State 6, line 1
> Arithmetic overflow error converting float to data type numeric.
> Statment terminated
> The values in [dpst_amt] field are numbers like 693.399999999998, if I try
> [select round(dpst_amt,2)] the value never is rounded, if I try to copy
> the value out to X field it fails as well. How did I determine what is in
> these fields, meaning how do I find the bad record? One must be bad but
> where or how do I find it?
> Thank you!
> Chris
>|||Originally the datatype was Float and what I've found since writing this was
the value of [1.#INF] was stored in the field. How does one search for that
value in the future?
Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eukytqyWFHA.2912@.TK2MSFTNGP10.phx.gbl...
> What is the size of your decimal datatype? There must be some data in your
> column that is larger than the largest that fits in the decimal datatype,
> so if your decimal is for example decimal(6,2)
> SELECT dpst_amt
> FROM your_table
> WHERE ABS(dpst_amt) > 9999.99
> should give you the values that are outside the range of your decimal.
> SELECT MAX(ABS(dps_amt)) FROM your_table
> will give you an indication as to what the size of your decimal datatype
> should be.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
>

Data Type Conversion

I have a SQL Server 200 database and I need to change the data types in a few fields in a table.

The fields are currently are NVARCHAR and need to be datetime

I get an error message when trying to do this:

****
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
****

My regional settings show as English UK and date format of dd/mm/yyyy.

Can anyone help on this issue?

Thanks in advance.

Neil.The obvious suggestion (and therefore probably wrong) would be to identify rows where the nvarchar data is not actually a valid date time.

I'm not a purist when it comes to cursors. My first approach would be to use a cursor. Scroll through the records and attempt to insert them (just the date/time column) into a new table. If you encounter an error, output sufficient data to clearly identify a row.

Remember that it takes only a single bad data point to trash the conversion process. There may not be that many bad data points.

Something else to remember about what SQL is doing in the background: it is actually creating a new temporary table with the date/time column, inserting the data, then dropping the old table and renaming the new temporary table to the old table name. Just food for thought.

Regards,

hmscott|||My first approach would be:

select *
from YourTable
where IsDate(YourTextDate) = 0

That should show you all the rows with invalid dates.

blindman|||Thanks!

I think I've solved the problem now.

Some of the dates are 2003 and some are 03 so if I change the 03's to 2003 and then convert the data type it works!

Thanks anyway!|||Oh, ugh. I better go back to the SQL BOL. I thought IsDate was only a VBScript function.

Sigh. So much to learn, so little time.

Thanks,

hmscott|||There's a lot of functions and tools out there. I frequently see people reference usefull things on this forum that I have to go look up.

It's too bad that when a new version comes out the help system doesn't have a section titled "Just the stuff you don't already know."

blindman

Data type

Hi,
I am trying to find the data types of the fields in my table "XYZ"....Since i have 140 fields in that table, i like to store a value in INT datatype.
Does anyone it on top of their head? Really appreciate the help...

Thanks.

Do you mean a one-time query to take a peek at the data types in your table? Try this:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable'
|||Thanks. Really appreciate your help..