Changing the design from char to datetime

I accidently put char instead of datetime in the Sql Server DateCreateddataType. Is there any way that now I can change. I guess even if Ichange I cannot get the time and date in a format that I want sincethey are in Char datatype.

you can change it in the design view or use the ALTER Table commandfrom thw Query analyzer. You can still change the datatype and be ableto use the datetime functions.
|||The problem is that the data in those fields have already been entered.And when I change it is not complient with the old data.

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.
TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark
|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegro ups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing datetime to string?

As in the database, i made a few columns in the forum table.
date(datetime) 15/09/2004 3.35PM
author(char) John

Select datetime + '<br>' + author from forum

it claimed there is an error on this datetime.

By right, the result should be

15/09/2004 3.35PM
John

can anyone help me how i could get the result out without having to change date's properties in the sql database?

Will be greatly appreciated if help gets ard.It looks like that select statement is trying to perform a math function. You should select the fields individually, then format them appropriately in your vb/cs code.

select datetime, author from forum

In your code, you will now have two fields exposed, and you can concatenate them if you wish.|||The TSQL CONVERT function can do that for you or Google for using string.format and use a date format code to convert it to the type of string you want it to be from within your VB code.

Changing date and keeping time

Hi! I'm using MS SQL server 2000. How can I change date in date field keepin
g
time the same (datetime field)? I'm using SQL Enterprise Manager for that.
Please help with syntax. More thanks, Alar. PS! Can You suggest some book or
other source I can find hints about MS SQL syntax?
> Hi! I'm using MS SQL server 2000. How can I change date in date field
> keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
That wont work, if you edit the date via EM you always will (implicit)
issue a command like:
UPDATE Sometable SET Somecolumn '01/01/2004 00:00:00' Where ...
even if you type in '01/01/2004'. The function DATEADD or if you prior cut
out the time and put it on the changed column (time 00:00:00) would work
fine.
PS! Can You suggest some book or
> other source I can find hints about MS SQL syntax?
Did you try BOL ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||> Hi! I'm using MS SQL server 2000. How can I change date in date field
keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax.
See function CONVERT in BOL.
Example:
update table1
set c1 = '2005-05-09' + right(convert(varchar(25), c1, 126), 13)
where c1 >= '20050501' and c1 < '20050508'
AMB

> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?
MS SQL Server 2000's Books Online
AMB
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||The DateAdd() function can add arbitrary number of days, ws, months, or
whatever, to a given date. If you choose any time increment greater than a
day, the time portion of the value will remain the same...
Otherwise, you need to update the column to a new date with the same time as
the the datetime that's in there...
Update TablleName Set
DTColumn = 'NewDate as CCYYMMDD ' +
convert(VarCHar(12), DTColumn, 14)
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||I don't recommend using Enterprise Manager for this, and
I suggest you run the update query in Query Analyzer
instead.
If @.newDate is the new date, T is your table, myDate is the
column you want to change, and rowKey = @.rowKey identifies
the row you want to change, this will work (not tested - watch
for typos)
update T set
myDate = dateadd(day, datediff(day, myDate, @.newDate), myDate)
where rowKey = @.rowKey
This will add a whole number of days to myDate, the number
it adds being exactly the number of whole days from myDate
to @.newDate.
Steve Kass
Drew University
Alar Pandis wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?

Changing datatype from char to datetime

I am trying to run the following query:

ALTER TABLE dnb_profile
ALTER COLUMN [family update date] datetime

and I keep getting the following error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated.

Can anyone tell me how I can do this successfully??

Thanks,

Connie Sawyer
Foley & Lardner
clsawyer@.foley.comOn 27 Sep 2004 09:15:29 -0700, Connie Sawyer wrote:

>I am trying to run the following query:
>ALTER TABLE dnb_profile
>ALTER COLUMN [family update date] datetime
>and I keep getting the following error:
>Server: Msg 242, Level 16, State 3, Line 1
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value.
>The statement has been terminated.

Hi Connie,

This indicates that at least one value currently in the [family update
date] column is of a format that won't convert to SQL Server properly.
There may be various explanations:

1. Someone managed to enter some gibbledygook in the column - possible,
since it's of the char data type. True rubbish would result in another
error message, but dates like february 30, december 53 or some date in
month number 17 would yield this message.

2. The contents of the column may look like normal dates to you, but not
to SQL Server. The error message you got is quite common if SQL Server
interprets day as month and month as day. Remember that there are manu
different notation styles for dates. The only unambiguous date formats are
yyyymmdd (for date only) or yyyy-mm-ddThh:mm:ss.mmm (for date and time,
where .mmm, denoting the milliseconds, is optional).

In each case, you'll have to inspect your data to find the cause and
either manually fix the offending rows (if there are just a few) or do
some string massaging to change from a misunderstood date format to one of
the standard formats before converting.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Connie Sawyer wrote:

> I am trying to run the following query:
> ALTER TABLE dnb_profile
> ALTER COLUMN [family update date] datetime
> and I keep getting the following error:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> The statement has been terminated.
> Can anyone tell me how I can do this successfully??
> Thanks,
> Connie Sawyer
> Foley & Lardner
> clsawyer@.foley.com

What you should do is to run this:

SELECT * FROM dnb_profile
WHERE ISDATE([family update date])=0

This will return you all the records
where value of [family update date] can't be converted to date.
And you should fix those records before altering the column.

Here's the link to the isdate function:

http://msdn.microsoft.com/library/d..._ia-iz_8ov9.asp

WYGL,
Andrey

Changing data type from Char to Datetime

Thanks in adance

Platform: SQL 2000

A SQL table has a field named "pay-day" with Char(8) data type.

I tried to change its data type to datetime but only with an error message like this. I did right-click the table and tried to modify a data type.

- Unable to modify table.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Jay

This means that some of your data in that column cannot be implicitly converted to a datetime value.

Here is some more info on CAST and CONVERT -- you may want to try using these functions specifically to help identify which data is out of range.

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

Hope that helps

-Steve

|||

Being that all of the data in your table is valid date data:

It seems that your field (8) has a date like mmyydd if this is correct,

It would seem that SQL thinks this is a number which it want's to convert to a date.

Create new field and write a stored proceidure to update the new field to your field converted to date.

format your field using format(yourField,"mmyydd") then insert into the new date field.

When you convert SQL has to know what date format you are starting from.

|||

The conversion is not working because some of the data has non-conforming values -values that do NOT convert to valid dates.

You will need to locate the non-conforming values and correct them first. The following query should help you find them.

SELECT [Pay-Day]
FROM MyTable
WHERE isdate( [Pay-Day] ) = 0