• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

SQL DateTime conversion

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
OB1Canobie
Asked:
OB1Canobie
  • 4
  • 4
  • 2
  • +1
1 Solution
 
HainKurtSr. System AnalystCommented:
start posting your code here ;)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
find those incvalid dates with this query

select datecolumn
from yourTable
where isdate(datecolumn) = 0
0
 
HainKurtSr. System AnalystCommented:
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
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.

 
OB1CanobieAuthor Commented:
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
 
OB1CanobieAuthor Commented:
Is the error happening because there is no date and just time information?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>These are not dates, but time stamps

So how will you interpret this
215235

is that in hhmmss format
0
 
OB1CanobieAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
OB1CanobieAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:


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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now