| Author |
 Topic  |
|
|
cidr
Posting Yak Master
United Kingdom
191 Posts |
 Posted - 12/07/2012 : 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
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
3328 Posts |
Posted - 12/07/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 12/07/2012 : 07:04:29
|
SELECT CONVERT(DATE,(date),103)
FROM tblname |
|
|
|
cidr
Posting Yak Master
United Kingdom
191 Posts |
Posted - 12/07/2012 : 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
United Kingdom
191 Posts |
Posted - 12/07/2012 : 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
:) |
Edited by - cidr on 12/07/2012 07:11:35 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 12/07/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/07/2012 : 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
India
47036 Posts |
Posted - 12/07/2012 : 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/
|
|
|
| |
Topic |
|
Datatype conversion problem
