January 17, 2008 at 10:28 am
i have some fields in SQL Server table as nvarchar(50) and the user actually enters date (example : 02/05/07) now they want those fields to be converted to datetime or small datetime field.
How do i achieve it without losing data?
January 17, 2008 at 10:42 am
If you are sure that the data in there are all valid dates, you can just modify it using SSMS to date feild.
Another option is to add another column with datatype Datetime and then populate it with a convert to Datetime. Drop the old column and rename the new column to the old column
-Roy
January 17, 2008 at 10:44 am
Try this:
Without sampling a large population of your nvarchar date data, I will assume the example you gave will be sufficient.
Here I simulate your scenario.
declare @TestDateValue nvarchar(50)
set @TestDateValue = '01/01/05'
select cast(@TestDateValue as datetime) as NewTestDateTime
The variable @TestDateValue is now a datetime. If milliseconds are not necessary, you could also use:
select cast(@TestDateValue as smalldatetime) as NewTestDateTime
I hope this helps.
-Mike
January 17, 2008 at 10:46 am
The best way, create a new column as a datetime column. Run a query to insert data into the new column, converting the data from the existing column into a datetime value. Drop the old column after all data is converted correctly, then rename the new column to the name of the old colun.
😎
January 17, 2008 at 10:53 am
Mike DiRenzo (1/17/2008)
Try this:Without sampling a large population of your nvarchar date data, I will assume the example you gave will be sufficient.
Here I simulate your scenario.
declare @TestDateValue nvarchar(50)
set @TestDateValue = '01/01/05'
select cast(@TestDateValue as datetime) as NewTestDateTime
The variable @TestDateValue is now a datetime.
Actually - no. You haven't done anything to @testdatevalue itself. The result in the NewTestDateTime is a datetime.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 10:55 am
I'm with Lynn - add column, run update process, drop old column.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 11:11 am
I do not disagree with you. It was a typo on my part. The easiest way to do it is as Lyn said. I was merely pointing out the SQL aspect and the intrinsic functions. But alas, my typo diminished my point. Thank you for your insight.
-Mike
January 17, 2008 at 1:48 pm
thanks for the information.
can you please tell me how to do update process?
January 17, 2008 at 2:01 pm
As Lynn and Matt pointed out, adding a new column and updateing will solve your problem. My example assumes you are using some version of Query Analyzer where you are able to create SQL and execute it.
There are two ways to approach this. The first way is the easiest.
1)
alter table TABLE1
alter column COL1 datetime
Where TABLE1 is the particular table in questions and
Where COL1 is the name of the column containing all of your nvarchar(50) data.
Done.
2)
I am creating a dummy table table to simulate your table of data.
create table tt
(
col1 nvarchar(50)
)
I am inserting rows with data that have a datatype of nvarchar(50) to simulate your data.
insert tt
select '01/01/2008' union
select '01/02/2008' union
select '01/03/2008' union
select '01/04/2008' union
select '01/05/2008' union
select '01/06/2008' union
select '01/07/2008'
I am adding the new column of datatype datetime that Lyn and Mat were talking about.
alter table tt
add NewDateCol datetime
Now I fire an update statement to produce values of type datetime using the nvarchar data.
update tt
set NewDateCol = cast(col1 as datetime)
I am selecting the data to view and verify.
select * from tt
I hope this works.
January 21, 2008 at 9:06 am
I have the same problem, with my data imported as nvarchar in the format MMDDYYYY (01012008, for example.) I get an out-of-range datetime value when I attempt the alter column solution, which I gather means SQL doesnt recognize the field format as valid for conversion to datettime. Any ideas of now to get it into recognizableformat?
January 21, 2008 at 9:32 am
Try this:
cast(substring(TheDateColumn,5,4) + substring(TheDateColumn,1,2) + substring(TheDateColumn,3,2) as datetime)
😎
January 21, 2008 at 9:37 am
Worked like the proverbial charm....thanks for the quick reply, you guys are great
January 21, 2008 at 10:03 am
Your welcome.
That's just one way. I'm sure someone else has another an will share it. There are usually more than one way to skin a cat in SQL.
😎
January 21, 2008 at 10:32 am
Hey there,
use set dateformat (dmy) or ymd etc... before you use your cast or convert statement in your query. This will eliminate dates that will be out of range.
January 22, 2008 at 2:59 am
Nick
Not really. From BOL:
Remarks
This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.
Dates can be stored as character data (usually) in either US (MDY) or UK (DMY) format, never a mix of both because of ambiguities - the date string '02012008' is the first of February in US format, in UK format it's the second of January. Both are valid dates but injudicious use of SET DATEFORMAT could result in a datetime conversion which is valid but incorrect.
The statement should be issued once, if necessary, to match the DMY order in the string, before starting conversion. Changing the SET DATEFORMAT setting during a conversion to enable a single value to convert to a valid date can only give you an unreliable valid date - you won't be able to tell what the date was meant to be.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply