SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Datatype conversion problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 12/07/2012 :  06:52:35  Show Profile  Reply with Quote
Hi there,

I'm looking for a bullet proof way of converting dates within a table that are stored as NVarchar.

The issue I appear to be having is two date fields. one date field holds only the date is Nvarchar (no time). The other field holds the date and the time.

This is the error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

Error with field with datetime using 102
Convert(Datetime,Date,102)
27/07/2012 10:49

Error with field with just date using 103
Convert(Datetime,Date,103)
31/05/2012

I want to make the conversion as bullet proof as possible and have the same conversion for each field in the queries.

incidentally, I can't change the columns to datetime yet so any help on the query side will be appreciated

Edited by - cidr on 12/07/2012 07:01:51

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/07/2012 :  07:00:49  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Depends on what your data is like.
103 will handle anything with dd/mm/yyyy - so both your examples.
If you have data which is mm/dd/yyyy then you will get an error or worse an incorrect date.
A date in the format yyyymmdd will always be converted implicitely as will yyyy-mm-ddThh:mm (but the second one needs the time whereas for the first it is optional.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 12/07/2012 :  07:04:29  Show Profile  Reply with Quote
SELECT CONVERT(DATE,(date),103)
FROM tblname
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 12/07/2012 :  07:05:43  Show Profile  Reply with Quote
Both fields use dd/mm/yyyy regardless of the time. The field with no time fails with 103 so it 103 wont convert all the time as I've mentioned.

It would be nice to see examples of conversions that people have used that are solid for both fields.

Cheers
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 12/07/2012 :  07:07:15  Show Profile  Reply with Quote
Thanks for the reply shilpash.
quote:


shilpash
SELECT CONVERT(DATE,(date),103)
FROM tblname





If you re-read my post, you'll see I'm complaining about 103 not converting the date field with no time.

I'm not using 103 or 102

:)

Edited by - cidr on 12/07/2012 07:11:35
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 12/07/2012 :  07:26:29  Show Profile  Reply with Quote
First execute case1, then run the case2
See difference
case1:
SET dateformat mdy
DECLARE @date1 nvarchar(40) ='31/05/2012', @date2 datetime = '27/07/2012 10:49:00'
select @date1, CAST( @date2 AS DATE), Convert(Date,@date2,103)

case2:
SET dateformat dmy
DECLARE @date1 nvarchar(40) ='31/05/2012', @date2 datetime = '27/07/2012 10:49:00'
select @date1, CAST( @date2 AS DATE), Convert(Date,@date2,103)


--
Chandu
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/07/2012 :  07:58:40  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
quote:
Originally posted by cidr

Both fields use dd/mm/yyyy regardless of the time. The field with no time fails with 103 so it 103 wont convert all the time as I've mentioned.

It would be nice to see examples of conversions that people have used that are solid for both fields.

Cheers




Try this - it should work with or without the time
declare @t table (s nvarchar(1000))
insert @t select '25/11/2012'
insert @t select '25/11/2012 08:12'
insert @t select '25/11/2012 08:12:12'
insert @t select '25/11/2012 08:12:12.567'

select convert(datetime, s, 103) from @t


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/07/2012 :  08:31:54  Show Profile  Reply with Quote
quote:
Originally posted by cidr

Both fields use dd/mm/yyyy regardless of the time. The field with no time fails with 103 so it 103 wont convert all the time as I've mentioned.

It would be nice to see examples of conversions that people have used that are solid for both fields.

Cheers




why do you sent dates in ambiguos formats? Always try to use unambiguos format like iso to avoid date conversion issues

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000