Convert strings to datetime

[jjkatz]

I have a query that determines a product's date of manufacture from the serial number. The day is formatted as a day number (e.g. "107" = April 17) and the year as a two-digit year. All dates can be assumed to be 21st century.

I need to take this data, which is in two separate fields in varchar format, and represent it as a date in mm/dd/yyyy format. So for example, if the field values are "107" and "06" I need to display "04/17/2006."

Thanks in advance for the correct formula!

[mikr0s]

Suppose flddays is the field of table1 that repsresents number of days and fldyear is the field that represents the year.

SELECT flddays, fldyear, DATEADD(dd, flddays-1, '01-01-20'+fldyear) AS the_date
FROM table1


--HTH--

[jjkatz]

I got the following error:

Server: Msg 245, Level 16, State 1, Line 8
Syntax error converting the varchar value '01-01-20' to a column of data type int.

The entire query is below. That might make it easier to see where I'm trying to go and how I'm trying to get there:

select Expiration_Day = CASE WHEN LEFT(Serial_Number, 3)< 336 THEN LEFT(Serial_Number,3)+30 ELSE LEFT(Serial_Number, 3) - 335 END,
Expiration_Year = CASE WHEN LEFT(Serial_Number, 3)< 336 THEN SUBSTRING(Serial_Number, 5,2) ELSE SUBSTRING(Serial_Number, 5, 2)+1 END
into #tmp_Expiration
From Assets

SELECT Expiration_Day, Expiration_Year, DATEADD(dd, Expiration_Day-1, '01-01-20'+Expiration_Year) AS the_year
FROM #tmp_Expiration

drop table #tmp_Expiration

[jjkatz]

I modified my query as follows:

select Serial_Number, Expiration_Day = CASE WHEN LEFT(Serial_Number, 3)< 336 THEN LEFT(Serial_Number,3)+30 ELSE LEFT(Serial_Number, 3) - 335 END,
Expiration_Year = CASE WHEN LEFT(Serial_Number, 3)< 336 THEN CAST('20'+SUBSTRING(Serial_Number, 5,2)AS VARCHAR) ELSE CAST ('20'+SUBSTRING(Serial_Number, 5, 2)+1 AS VARCHAR) END
into #tmp_Expiration
From Assets

SELECT Serial_Number, Expiration_Day, Expiration_Year, CONVERT(varchar, CAST(Expiration_Year + DATEADD(dd, Expiration_Day, 0) AS DATETIME), 101)
FROM #tmp_Expiration

drop table #tmp_Expiration

Thanks for pointing me in the right direction!

[mikr0s]

I need to take this data, which is in two separate fields in varchar format, and...

The : '01-01-20'+fldyear works only when fldyear is varchar. In your code (2nd post) , SUBSTRING(Serial_Number, 5,2) points to varchar but ELSE SUBSTRING(Serial_Number, 5, 2)+1 points to numeric data type !!!
The Expiration_Year field in temporary table is numeric that's why the code raised an error...
Anyway, the following code is working no matter the data type (varchar or numeric) of flddays and fldyear:

SELECT flddays, fldyear, DATEADD(dd, flddays-1, DATEADD(yy, fldyear-1, '01-01-2001')) AS the_date
FROM table1

--HTH--