February 17, 2004 at 10:50 am
I have date data stored as an integer ( e.g. 20040213 = 02/14/2004 ).
I need to create a job which will examine the status of a record by its date;
is there a conversion function which will allow me to query the integer date
value from a date input?
February 17, 2004 at 11:11 am
Try CAST or CONVERT. For more info see http://www.sqlservercentral.com/columnists/sjones/castyourdata_printversion.asp
Francis
February 17, 2004 at 11:17 am
What kind of comparisons are you going to do?
I use integer for weeks ie week 32 of year 2003 is written as integer 200332 which makes the math simpler than having do use the datepart function.
Is the year always the first 4 digits of the integer? If so you could create a datefield by converting your number:
create table test2
(datefield datetime,
intfield int)
insert into test2
values(null, 20040213)
update test2
set datefield = cast(substring(cast(intfield as char(8)),5,2)+'/'+ right(cast(intfield as char(8)),2)+'/'+left(cast(intfield as char(8)),4) as datetime)
select * from test2
2004-02-13 00:00:00.000 20040213
Michelle
February 17, 2004 at 11:35 am
Since yyyymmdd is actually a valid date format (ISO), SQL server should be smart enough to know it's a date if it's simply treated as char. So, in mimorr's solution simply
February 17, 2004 at 11:36 am
Simply update test2 set datefield = convert(char, intfield) should be sufficient. (pressed ENTER too soon 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply