Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Convert nvarchar to datetime Expand / Collapse
Author
Message
Posted Thursday, January 17, 2008 10:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:16 PM
Points: 237, 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?
Post #444457
Posted Thursday, January 17, 2008 10:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
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
Post #444466
Posted Thursday, January 17, 2008 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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
Post #444467
Posted Thursday, January 17, 2008 10:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 22,472, Visits: 30,138
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.




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)
Post #444469
Posted Thursday, January 17, 2008 10:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,081, Visits: 14,670
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?
Post #444473
Posted Thursday, January 17, 2008 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,081, Visits: 14,670
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?
Post #444476
Posted Thursday, January 17, 2008 11:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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
Post #444486
Posted Thursday, January 17, 2008 1:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:16 PM
Points: 237, Visits: 764
thanks for the information.

can you please tell me how to do update process?
Post #444586
Posted Thursday, January 17, 2008 2:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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.
Post #444595
Posted Monday, January 21, 2008 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 18, 2009 4:15 PM
Points: 3, 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?
Post #445514
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse