Database

converting nvarchar to smalldatetime

19-Oct-09 14:25

I'm waiting for a reply, but I don't have a backup, and my response will be then you don't have a client!

Lalit singh19-Oct-09 1:08

19-Oct-09 1:08

Hi friends.

Please tell me how to convert nvarchar to smalldatetime in sql server

Ashfield19-Oct-09 1:18

19-Oct-09 1:18

With the convert function.

declare @a nvarchar(20)
set @a = '1 jul 2009 15:30:45'
select convert(smalldatetime,@a)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh19-Oct-09 1:52

19-Oct-09 1:52

Hi Thanks For your reply ,

The thing is that i have one table in which date is stored in column(datataype nvarchar)
There are more than lacs of records in this table.

so i want to convert this field into following format

ORIGINAL FORMAT OF FIELD IN TABLE (DATATYPE:-NVARCHAR) => 30/11/2009

I WANT INTO FORMAT : 30112009
AND ANOTHE FORMAT : 30/11/2009(SMALL DATETIME)

Please help me out.

I tried the solution which you gave but its giving following error

"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.
"

Please help me out

Ashfield19-Oct-09 8:45

19-Oct-09 8:45

Not sure why you get this error.

declare @a nvarchar(20)
select @a = '30/11/2009' 
select convert(smalldatetime,@a)

gives me 2009-11-30 00:00:00, which is correct. How it is displayed (i.e. 30/11/2009 or 30112009) is down to your format mask, SQL Server simply stores the datetime value as a number.

Please, before posting any more questions around this, use the F1 key and read up on date time data types.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh19-Oct-09 18:40

19-Oct-09 18:40

What you said is very correct , i am not denying it. The thing is that i am inserting into destination column(smalldatetime) from source column(nvarchar) and the number of records are around 4lac.
I am inserting through sql script in query analyser. After insrting some 20k records it generates that "Arithmetic overflow error".

This was task given to me. thats why otherwise i could change my destination column to datetime. Its working.

But I am forcebly want it to smalldatetime as this was task for me.

_Damian S_19-Oct-09 19:04

_Damian S_

19-Oct-09 19:04

Have you done any data analysis to make sure that all the values you are trying to convert fall into the acceptable range for smalldatetime values?

Perhaps you have a record (around the 20K mark) that has a typo in the date, and instead of 30/11/2009 it's 31/11/2009 (which isn't valid) or 30/11/9999...

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Ashfield19-Oct-09 20:56

19-Oct-09 20:56

So you have an invalid date in there. Use the isdate function to find any that are invalid.

declare @a nvarchar(20)
set @a = '30/11/2009'
select isdate(@a)
-- returns 1
set @a = '31/11/2009'
select isdate(@a)
-- returns 0

Its not rocket science.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh19-Oct-09 23:08

19-Oct-09 23:08

You were correct there were dates in dd/mm/yyyy which were incorrect format according to ISDATE() function . So i converted it into mm/dd/yyyy format and it worked.

Now thing is that i just saved in date format only like '11/30/2009' but in table having column date (datatype smalldatetime) it stored like '11/30/2009 00:00:00.000'

I want to store only date (datatype=smalldatetime). Is there any way to store into sql server 2005 date column with date only excluding time.

Ashfield20-Oct-09 1:54

20-Oct-09 1:54

Lalit singh wrote:
Is there any way to store into sql server 2005 date column with date only excluding time.

No, as I said in an earlier post, sql server stores the date and time as a number, not in any format. Just use a format when you display it.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh20-Oct-09 2:04

20-Oct-09 2:04

Thank you very much for your valuable response.

Luc Pattyn19-Oct-09 3:29

Luc Pattyn

19-Oct-09 3:29

why would you choose smalldatetime when all you want to store is a date?

Smile | :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? 59.24% waren verstandig genoeg om NEEN te stemmen; bye bye viaduct.

Richard MacCutchan19-Oct-09 4:47

Richard MacCutchan

19-Oct-09 4:47

Questions such as this explain so much about the world we now find ourselves in Laugh | :laugh:

Mycroft Holmes19-Oct-09 14:31

19-Oct-09 14:31

From the discussion I gather that you are running up against the first problem of storing date data in a non date data format.

You have precicely 1 choice to fix this problem, change your data type to datetime NOW, if you use a work around to convert from nvarchar to datetime then you will always be performing that work around. STOP AND FIX YOUR DATA.

I suggest creating a new column in a datetime format and convert/copy the data to that field. You are already getting an error (arithmetic) so I know your nvarchar data is corrupt. I would recursively convert as much as possible and then manually update the rest.

server down

baala.m18-Oct-09 17:24

baala.m

18-Oct-09 17:24

let us take the server is down and all the data are corrupted. other than backup,replication, log shipping what can we do to get back the data or make the customer to proceed with the work ?

bala

Re: server down

Mycroft Holmes18-Oct-09 19:31

Order of evaluation for sub-queries

18-Oct-09 19:31

How many recovery methods do you want? Restore the database from the last backup. This is the tried and true method of recovering from a complete database failure.

Re: server down

scottgp19-Oct-09 3:06

scottgp

19-Oct-09 3:06

Type everything back in from scratch?

Steve Holdorf18-Oct-09 9:52

Steve Holdorf

18-Oct-09 9:52

I am going through the msdn sql book and am running a very advanced query with a lot of sub-queries. In the book it does not explain the sub-queries in detail. It only shows the query and gives a result set. I am posting the query below so it can be seen. When I run it I am not getting the same results. First, I am not sure of the order of evaluation of each of the sub-queries (i.e. which get run first) and second I don't quite understand the table aliasing being used in the query. If some one could help me understand what is exactally going on, or point me in the right direction that would be great! Query below:

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM Orders AS o1
WHERE OrderDate = (SELECT MAX(OrderDate) AS Expr1
FROM Orders AS o2
WHERE (EmployeeID = o1.EmployeeID) AND
(RequiredDate = (SELECT MAX(RequiredDate) AS Expr1 FROM Orders AS o2
WHERE EmployeeID = o1.EmployeeID) AND (OrderDate = o1.OrderDate)
AND (SELECT MAX(OrderID) AS Expr1 FROM Orders AS o2
WHERE (EmployeeID = o1.EmployeeID) AND ((OrderDate = o1.OrderDate)))))))

I am not saying anything bad about the book but I am just learning advanced querying and someone who is an expert providing a little assistance would be great.

Thanks,

Steve

Not Active18-Oct-09 10:45

Not Active

18-Oct-09 10:45

It won't help immediately but a very good reference book for this subject is Inside Microsoft® SQL Server® 2008: T-SQL Querying[^]

only two letters away from being an asset

i.j.russell18-Oct-09 11:27

i.j.russell

18-Oct-09 11:27

Is this what you were trying to do?

SELECT o1.OrderID, o1.CustomerID, o1.EmployeeID, o1.OrderDate, o1.RequiredDate
FROM Orders AS o1
WHERE o1.OrderDate = (SELECT MAX(o2.OrderDate)
		      FROM Orders AS o2
	       	      WHERE o2.EmployeeID = o1.EmployeeID) 
AND o1.RequiredDate = (SELECT MAX(o3.RequiredDate)
                       FROM Orders AS o3
                       WHERE o3.EmployeeID = o1.EmployeeID
                            AND o3.OrderDate = o1.OrderDate)
AND o1.OrderId = (SELECT MAX(o4.OrderID) 
                  FROM Orders AS o4
                  WHERE o4.EmployeeID = o1.EmployeeID
                  AND o4.OrderDate = o1.OrderDate)

I find it much easier to read if I indent the code and explicitly use table alias'. The intent of the sql becomes much clearer.

Steve Holdorf18-Oct-09 13:03

Steve Holdorf

18-Oct-09 13:03

Your ordering has made it much more clear. Thanks for that! One last question. Do I read the subqueries from top to bottom or bottom to top. What is the order of execution?

Much thanks,

Steve Holdorf

Mycroft Holmes18-Oct-09 15:00

18-Oct-09 15:00

There is no "order" invoked by the positioning of the subqueries. SQL will build an execution plan (you REALLY need to study and understand these to get some performance enhancements) that is based on the cost of each query.

I have a base rule that says I use the strongest (returns the least row) filter first but I doubt it makes a difference.

Understanding what an inner join query produces

i.j.russell18-Oct-09 22:31

i.j.russell

18-Oct-09 22:31

I use the same approach because it helps clarify my intent but as you say it actually doesn't have any impact upon the query. The Sql Server Query Optimizer will determine the most efficient route based on the latest statistics.

Steve Holdorf17-Oct-09 19:02

Steve Holdorf

17-Oct-09 19:02

Below is a multi inner join query that works. The problem is that I don't understand how it is evaluated and need help understanding what it is doing. Can someone explain in detail what the query is doing because I don't quite understand?

SELECT DISTINCT o.OrderDate, c.CompanyName, od.Quantity, p.ProductName
FROM Customers AS c INNER JOIN
Orders AS o ON o.CustomerID = c.CustomerID INNER JOIN
[Order Details] AS od ON od.OrderID = o.OrderID INNER JOIN
Products AS p ON p.ProductID = od.ProductID

Thanks,

Steve Holdorf

Re: Understanding what an inner join query produces

Mycroft Holmes17-Oct-09 20:50