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
You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.
Code Snippet
setDATEFORMAT ymd
selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2
|||You could use CONVERT function with 112 style:
Code Snippet
declare @.dt varchar(20)
set @.dt ='20070507'
selectconvert(datetime,@.dt,112)
|||Hello again,
Thanks for your advise.
I tried to run the following code:
selectconvert(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
|||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
|||
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.
|||Change your code to:
Code Snippet
select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt
into WHUser.[tbl_target]
from WHUser.[tbl_source]
Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors
