?
Solved

SQL DateTime conversion

Posted on 2009-12-24
12
Medium Priority
?
279 Views
Last Modified: 2012-05-08
I am trying to copy a varchar field in a source table to a new table field that is DateTime.  I cannot complete the process because I get an error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." How can I find the data causing me problems?
0
Comment
Question by:OB1Canobie
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26120201
start posting your code here ;)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26120202
find those incvalid dates with this query

select datecolumn
from yourTable
where isdate(datecolumn) = 0
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26120212
for example convert a string to datetime, here are some samples

SELECT convert(datetime, '10/23/2016',          101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23',          102) -- yyyy.mm.dd
SELECT convert(datetime, '23/10/2016',          103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016',          104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016',          105) -- dd-mm-yyyy

check here : http://www.sqlusa.com/bestpractices/datetimeconversion/

for more options...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:OB1Canobie
ID: 26120221
When I ran the following query, it returned times that are marked as invalid.  Why would these be invalid?  I have attached some results.  These are not dates, but time stamps.

215235
215235
215235
215239
215250
215250
215258
215258
215303
215325
215330
215333
215404
215406
215408
USE
Dialer

SELECT
CALL_TIME

FROM
Stage_Dialer_History

WHERE 
isdate(CALL_TIME) = 0

ORDER BY
CALL_TIME ASC

Open in new window

0
 

Author Comment

by:OB1Canobie
ID: 26120232
Is the error happening because there is no date and just time information?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26120236
>These are not dates, but time stamps

So how will you interpret this
215235

is that in hhmmss format
0
 

Author Comment

by:OB1Canobie
ID: 26120245
Yes, the data is in hhmmss.  How do I insert this information without a date, or do I need to insert a date with the time?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26120269
you cannot insert only time part alone into the datatime column, all you can do is insert it as a datetime (so it will be 1900-01-01 21:52:35 )
0
 

Author Comment

by:OB1Canobie
ID: 26120275
So, I should combine a date and time field into one?  I'm guessing I will need to add the hyphens, spaces, and colons to make the insert work correctly?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26120284


SELECT CAST ('1900-01-01 '+ STUFF(STUFF( CALL_TIME, 5,0,':' ), 3,0, ':' ) as datetime )
FROM Stage_Dialer_History
WHERE  isdate(CALL_TIME) = 0
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 26120321
As Aneesh said.

You could do this :

              USE Dialer;

SELECT DATEADD(second, CALL_TIME * 36/100 + (CALL_TIME % 10000) * 6/10 + ((CALL_TIME % 10000) % 100), 0)
FROM Stage_Dialer_History
WHERE isdate(CALL_TIME) = 0
ORDER BY CALL_TIME ASC            
;

Hope that helps.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 26120348
Good job, Aneesh.
OB1Canobie: (firstly, I love that name!) You may also want to double check that you don't have a CALL_DATE field in the same table.  Might be helpful if you have lots of data for long periods of time, putting all under one date may be an issue later. :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question