hi, How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003 1/13/2007 01132004 1-1-2004 1.2.2001
there is no uniformity of the data that is stored currently.
We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?
Thanks ahead of time DMW
Edit: We are running SQL Server 2000 and the db at the time was running in simple mood
Hi, I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).
This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?
Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR
My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?
Current Code:
Code: CAST(P.CreatedDate AS VARCHAR) AS DateEntered,
Hi Everyone,I have a question about dynamically changing the length of a varchar(n)field, in case the value I'm trying to insert is too big and will givea "truncated" error, but before the error is given! i.e. Is there somekind of a way to "test" the length of the field while Inserting thevalue into it, and to have it automatically increase its length to thelength of the value being inserted, in case the value is too big?I've been able to do this in a "primitive" way, simply by identifyingthe specific error number in case the value is being truncated, andthen increasing the length of the varchar(n) field by using the ALTERcommand, and then duplicating the insert statement, but is there astandard (shorter) way of doing this?Here is my code (I'm working in an ASP environment):<%var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"On Error Resume Nextconn.Execute sqlIf err = -2147217833 ThenResponse.Write "Error Recognized Successfully!<br /><br />"sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &Len(var_txt) &") NOT NULL"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "Error while trying to alter Column:<br/>" & err & "= " & err.description & "<br />"ElseResponse.Write "Column altered successfully to: " &Len(var_txt) &"<br />"sql = "Insert Into Table1 (text) Values ('" & var_txt &"')"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "<br />Error number 2:<br />" &err.description &"<br />"ElseResponse.Write "Now it was added successfully!HaHa!<br />"End IfEnd IfElseResponse.Write "Success."End If%>Thanks in advance!
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Hi: I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime. I use Query like: SELECT CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +CAST(SUBSTRING(date_sent,4,2) as int) + '/' +CAST(SUBSTRING(DATE_SENT,7,4) as int) From MyTable It is not working, anybody can give me some advise! thanks!
I'm facing a small issue with date conversions. It would have been great if someone could help me out. I have a field in my database (SQL Server 2000) called SavDateTime of type varchar(50). I store dates in this field in the format "dd/MM/yy hh:mms". A sample date would be "23/10/2007 10:15:30 AM". Now I need to have an order by for this field, say like,
select * from sample order by SavDateTime desc
When I execute this query I get an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I tried using the convert method also like CONVERT(DATETIME,SavDateTime,103), but the problem still exists. It would have been helpful if you could provide me with a solution to this problem.
I am asked to compare the address fields (three columns of nvarchar(100) ) of a customer database (around 10,000 records) and find any duplicates. If it is a character by character match, I could have just GROUPed and get the result.
But, I am expected to produce a list with similar addresses which the guys who entered may have use slightly different spelling or more or less characters, or a "." here and there.
I have a textbox that is receiving a date in the format mm/dd/yyyy. The field in the database is VarChar size 10. I purposely don't want to use the DateTime data type for various reasons. When I extract the text from that textbox: Example:4/11/2005 I do my INSERT. When I look in the database at that field, it shows up like:April 11, 2005 12 The database's field is VarChar! How/why is it doing automatic DateTime formatting?
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code: Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID Where D.SchoolID = @DojoID and C.Days like'%' + @Days + '%' Union Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID Where D.SchoolID = @DojoID and @Date Between E.EventStartDate and E.EventEndDate
Hi,I want to change 2 default behaviors of ms-sql server within a connection.1. All varchar (etc.) fields should be returned as UTF-8.I know about field attribute collation and convert function,but I want simple query like:SELECT * FROM table;to return all varchars as UTF-8, no matter how the fields weredefined and which collation.In MySQL I execute: "SET NAMES 'utf-8';" after connecting to serverIn PgSQL I use native function: pg_set_client_encoding()2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"I just want all datetimes fields were returned as:"2008-04-20 12:01:02"I can do that with function convert() (style=20) but I want(as in question above) a simple query like:SELECT * FROM table;to return date and time formatted as described above.In MySQL and PgSQL the format "2008-04-20 12:01:02" is the defaultone.Is it possible to do that? If yes, please tell me how can I do that?Wladyslaw
Hi, I have a varchar field named FinancialYTDCode containing data in the format 2007F or 2008F. I want to only select the rows with the FinancialYTDCode that is the same as the current year.
Could someone please show me how I write this in my script. Thanks
Basically, I'm trying to take two dates and find the difference in hours b/t the two, sql server 2000. I've tried change the variables out from datetime to varchars even though it shouldn't matter. When I put the block of code out of the stored procedure and run it in line it works fine, I'M LOST.........
I get the errors everytime it runs into the first block, I get the error again when the block is hit again farther down in the procedure. I get:
Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ERR dates Aug 4 2005 11:16PM. Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ERR dates Aug 3 2005 12:35PM. Server: Msg 241, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 211 Syntax error converting datetime from character string.
IF @NextStatusDate IS NOT NULL AND @StatusDate IS NOT NULL BEGIN set @tmpTimeCharVar = DATEDIFF(SECOND, @NextSTatusDate, @StatusDate) set @tmpTimeVar = ( CASE WHEN @tmpTimeCharVar/3600<10 THEN '0' ELSE '' END + RTRIM(DATEDIFF(SECOND,@NextSTatusDate, @StatusDate)/3600) + ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) / 60),2) + ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) % 60),2) ) END IF @@ERROR <>0 PRINT 'ERR dates' + Convert(varchar(20), @NextStatusDate) + '.'
1. Copy old data from each table in LiveDB to same table in ArchiveDB. 2. Delete the data from each table in LiveDB which is in ArchiveDB
Both DBs SIMPLE recovery mode.
Each table has a clustered PK on a single int value. In both DBs
The tables with varchar(max) columns are taking a v.long time to copy over.
IS there anything I can change in the ArchiveDB to make it run faster.
It is the insert that is taking the time. I've tried dropping the clustered PKs in ArchiveDB tables and then rebuilding afterwards but it has not made any difference. After all I am adding data to the ArchiveDB in clustered index order, so wouldn't have expected it to.
How I can change the Archive DB but cannot touch the schema/settings of Live DB.
