Extracting date & time from a nvarchar field

Please see attached query in Word
ee-query.doc
ejr19592004Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
update dataset set eventdate = convert(datetime, nullif(left(eventdatetime,8),''),3)

update dataset set eventtime = convert(datetime, nullif(right(eventdatetime,11),''),9)
0
 
ong-hhCommented:
have u try this
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)
0
 
ejr19592004Author Commented:
Got this:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
james-ct16Commented:
Howdy
Having gone through your word doc i think you may have a couple of issues to address. With having a smalldate date and a small date time for the same date for each row you are storing the same data three times, once as the varchar and twice as a small date. You cannot use the small date as time only or date only. Date and Time data types are not introduced until sql 2008.

To get the data formated as you want the following query should work

SELECT
convert(varchar, cast('01/10/10 01:00:04' as smalldatetime),103),
convert(varchar, cast('01/10/10 01:00:04' as smalldatetime),108)

So I would store the date as a datetime type and then format as required, if formating is required for reporting I would consider a view with date and time formated as per the query above.

Hope that helps
0
 
ejr19592004Author Commented:
@cyberkiwi had high hopes for this, but I got this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
0
 
cyberkiwiCommented:
I tried it for every single row you listed, in fact I have even verified it on SQL Server 2000/8.
Do you have any other formats of data that is not as shown?

Maybe it is your language setting..
0
 
cyberkiwiCommented:
Maybe, for the first one, use

update dataset set eventdate = convert(datetime, nullif(left(eventdatetime,8),''),1)

The last 1 or 3 refer to whether it is dd/mm/yy (3) or mm/dd/yy (1)
0
 
ejr19592004Author Commented:
It might be. This bloody thing is trying my patience. Let me use the upsizing wizard from access to sql rather than the import/export in ss. see if that makes any diference. give me a minute.
0
 
cyberkiwiCommented:
select * from dataset
where len(eventdatetime) between 1 and 19

-- non conformists
0
 
ejr19592004Author Commented:
ok I ran the last one and i got this:

1      Transaction Time      NULL      NULL      Area Num
0
 
cyberkiwiCommented:
What do you know... that looks like some HEADER row...
Just delete it before even starting the updates.  try http:#a34235815 again, but use 1 or 3 for the first query depending on whether 1/10 is 1-Oct or 10-Jan
0
 
ejr19592004Author Commented:
code spot on. my mistake. 5 lines of rubbish crept into the file.
much obliged.
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.

All Courses

From novice to tech pro — start learning today.