SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert nvarchar to datetime


Convert nvarchar to datetime

Author
Message
keywestfl9
keywestfl9
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 764
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?
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8431 Visits: 6891
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
Mike DiRenzo
Mike DiRenzo
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 210
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92729 Visits: 38954
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.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28787 Visits: 19002
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?
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28787 Visits: 19002
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?
Mike DiRenzo
Mike DiRenzo
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 210
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
keywestfl9
keywestfl9
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 764
thanks for the information.

can you please tell me how to do update process?
Mike DiRenzo
Mike DiRenzo
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 210
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.
donald.jones
donald.jones
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 19
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search