Convert from nvarchar(25) to smalldatetime

aspless · August 15th, 2007, 10:52 AM

Chaps, really hope you can point me in the right direction

I have a table field called sqldate which currently has a data type of nvarchar and length of 25 which has date entryâs in dd/mm/yy.

So that i can successfully query the field i need to convert it to smalldatetime.

Since creating this field i now can successfully input date formats into smalldatetime which before now eluded me.

Any help would be appreciated.

Thanks

Aspless

dparsons · August 15th, 2007, 10:58 AM

Hmmm not sure if this is what you mean but something like this should work for you:

SELECT * from Table where Convert(smalldatetime,field) = @smalldatetimeparameter

But please explain to me this line:
Since creating this field i now can successfully input date formats into smalldatetime which before now eluded me.

and also why you are not just using a smalldatetime column.

hth,

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

aspless · August 15th, 2007, 04:10 PM

Thanks for the reply.

After reading the line

Quote:

quote:Since creating this field i now can successfully input date formats into smalldatetime which before now eluded me

i can see why you would be confused .. apologies, too many hours by the screen.

So that i can successfully query the sqldate field using the following code

Code:

SELECT * FROM atable where sqldate > DateDiff(Day, 14, GetDate())

i need to change using query analyzer the sqldate field from nvarchar to smalldatetime.

As there are already records in the format of dd/mm/yy if i try to change the format in sql design i get the error.

Code:

Arithmetic overflow error converting expression to data type smalldatetime

I am hoping there is a script which will allow me to convert the field with the entries in place.

I hope this makes more sense.

Cheers

aspless

dparsons · August 16th, 2007, 07:39 AM

Instead of trying to change the columns data type you could create a new column in the table with a smalldatetime type and just execute this query:

UPDATE Table set newDateTimeColumn = Convert(smalldatetime, varcharDateField)

Someone else may have a better solution but I do not.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

aspless · August 16th, 2007, 02:16 PM

Thanks for the reply.

I will try your method.

Cheers

aspless

aspless · September 14th, 2007, 09:05 AM

Just a quick update on the solution

Your suggestion did work with the addition of the date type specifier

UPDATE atable set newcolumn = Convert(smalldatetime, original_column, 103)

Cheers

dparsons · September 14th, 2007, 09:21 AM

Glad it worked out for you. ^^

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========