|
From: John H. <jh...@im...> - 2020-01-25 18:44:17
|
Agh, sorry, just noticed this was still sitting in my drafts folder...
On Wed, 22 Jan 2020, Elliott, David wrote:
> Hi,
>
> I have a bunch of dates from a legacy system that are numeric(8) fields, with the dates formatted as YYYYMMDD. They've been extracted as-is into staging tables in MS SQL Server 2012.
> I went looking for the best way to convert those into MS-SQL date fields, and came up with a variety of options.
> However, when I tested those options out in SQuirreL-SQL 3.9.1, they all came back 2 days off when I specified 'date' as the target data type:
>
> select cast('20200101' as date) ,
> cast(left(20200101, 8) as date) ,
> convert(date, cast(20200101 as char(10)), 112) ,
> convert(date, convert(char(8), 20200101)) ,
> convert(datetime, convert(char(8), 20200101)) ,
> dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0) ,
> convert(date,dateadd(day, datediff(day,0,cast(20200101 as varchar(10))), 0))
> /* results:
> 2019-12-30
> 2019-12-30
> 2019-12-30
> 2019-12-30
> 2020-01-01 00:00:00.0
> 2020-01-01 00:00:00.0
> 2019-12-30
> */
I'd strongly suggest you remove the ambiguity by reformatting them as
delimited strings in 'YYYY-MM-DD' format, rather than passing in a regular
number and hoping the various methods "do the right thing" with it.
CAST(
CAST((CAST(dtval AS integer) / 10000) AS varchar) + '-' +
CAST(((CAST(dtval AS integer) / 100) % 100) AS varchar) + '-' +
CAST((dtval % 100) AS varchar)
AS date
)
Error handling left as an exercise for the student...
--
John Hardin KA7OHZ http://www.impsec.org/~jhardin/
jh...@im... FALaholic #11174 pgpk -a jh...@im...
key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
We should endeavour to teach our children to be gun-proof
rather than trying to design our guns to be child-proof
-----------------------------------------------------------------------
Tomorrow: John Moses Browning's 165th Birthday
|
