Conversion of nvarchar to smalldatetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion of nvarchar to smalldatetime

Author

Topic

Paru J Maliyil
Starting Member

6 Posts

Posted - 2007-07-31 : 07:07:08

Hi,
when I convert a field into smalldatetime from nvarchar(50) in Sql Server 2000, i got a default value (1900-01-01 00:00:00) for that field.Actually value of that field to be changed as different values).I want to be convert bulk of records.please help me.

current data

pvdate(nvarchar(50))
------
12/03/2007

Data to be changed into

pvdate(smalldatetime)
--------------------
2007-03-12 12:00:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 07:22:44

What would you like it to be changed into?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-31 : 07:56:51

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 02:48:07

We know that, but the "default 1900-01-01" dates?

SELECT pvDate, CONVERT(DATETIME, pvDate), CASE WHEN pvDate = '12/03/2007' THEN CAST(0 AS DATETIME) ELSE CAST(pvDate AS DATETIME) END FROM Table1

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-01 : 02:54:04

1 Dont edit the question to add sample data. Post that as new reply
2 What is the format of the dates stored in nvarchar(50) column?
3 Always use proper DATETIME datatype to store Dates

Select dbo.Proper_date(replace(pvdate,'/','')) as pvdate from table

Use proper date function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Paru J Maliyil
Starting Member

6 Posts

Posted - 2007-08-01 : 03:11:44

select pvdate,convert(datetime,pvdate),case when pvdate='23/08/1999'
then cast(0 as datetime) else cast(pvdate as datetime) end
from tp1

Tested the above query and got the following error
---------------

(4 row(s) affected)

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

Paru J Maliyil
Starting Member

6 Posts

Posted - 2007-08-01 : 03:17:50

Select dbo.Proper_date(replace(pvdate,'/','')) as pvdate from tp

Tested the above query too;but didn't get any change

Error Msg got by the above query
--------------------------------

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Proper_date'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 03:39:09

dbo.Proper_date is a function written by madhi. you have to copy the code and run it on your database first.

or...

SET DATEFORMAT DMY

select pvdate,convert(datetime,pvdate),case when pvdate='23/08/1999'
then cast(0 as datetime) else cast(pvdate as datetime) end
from tp1

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-01 : 04:30:34

quote:
Originally posted by Paru J Maliyil

Select dbo.Proper_date(replace(pvdate,'/','')) as pvdate from tp

Tested the above query too;but didn't get any change

Error Msg got by the above query
--------------------------------

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Proper_date'.

I already specified

Use proper date function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Paru J Maliyil
Starting Member

6 Posts

Posted - 2007-08-01 : 05:36:07

Thanks a lot...The function 'proper_date' working very nicely...It changed my data from nvarchar to smalldatetime.
Very thanks for your help...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-01 : 05:44:56

quote:
Originally posted by Paru J Maliyil

Thanks a lot...The function 'proper_date' working very nicely...It changed my data from nvarchar to smalldatetime.
Very thanks for your help...

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources