ulrikeG


Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.



I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL

Any hint for me is very

Thanks in Advance

ulrike





Re: Dataconversion: STRING (yyyymmdd) to DATE

DaleJ


You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

set DATEFORMAT ymd

select convert(datetime, '20070604') as d1, cast('20070604' as datetime) as d2







Re: Dataconversion: STRING (yyyymmdd) to DATE

Konstantin Kosinsky

You could use CONVERT function with 112 style:

Code Snippet

declare @dt varchar(20)

set @dt = '20070507'

select convert(datetime,@dt,112)






Re: Dataconversion: STRING (yyyymmdd) to DATE

ulrikeG

Hello again,

Thanks for your advise.

I tried to run the following code:

select convert(datetime,whuser.[tbl_source].EffectiveDate_str,112) as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

¡°The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value¡±.

What¡¯s going wrong Please give me some mor advise.

Thanks in advance,

ulrike






Re: Dataconversion: STRING (yyyymmdd) to DATE

hunchback

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB





Re: Dataconversion: STRING (yyyymmdd) to DATE

fleo

ulrikeG wrote:

¡°The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value¡±.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.





Re: Dataconversion: STRING (yyyymmdd) to DATE

Tom Phillips

Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THEN convert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL END as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]






Then:
SELECT * FROM WHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors