Datatype conversion problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Datatype conversion problem

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2012-12-07 : 06:52:35

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
[code]
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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 07:00:49

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.

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-07 : 07:04:29

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

cidr
Posting Yak Master

207 Posts

Posted - 2012-12-07 : 07:05:43

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

cidr
Posting Yak Master

207 Posts

Posted - 2012-12-07 : 07:07:15

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

:)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-07 : 07:26:29

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 07:58:40

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 08:31:54

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources