Date Manipulation

ppp · Post by **ppp** » Sat Feb 27, 2010 9:29 am

I have input data which is numeric and stored in 4 different fields as follows.

Century : 20
Year : 9
Month : 1
Day : 5

This data has to be formatted to arrive at the date in the format
2009-01-05 or 2009-01-05-HH.MM.SS.NNNNNN

How can I achieve this?

chulett · Post by **chulett** » Sat Feb 27, 2010 9:42 am

What is your target? Database table? If so, what data type? I ask because of your "has to arrive in X format" statement, that won't be true for an actual Date field.

ppp · Post by **ppp** » Sat Feb 27, 2010 10:45 am

My target is a flat file and it is varchar.

anbu · Post by **anbu** » Sat Feb 27, 2010 11:16 am

Code: Select all

(Century*100 + Year) : '-' : right('00':Month,2) : '-' : right('00':Day,2)

chulett · Post by **chulett** » Sat Feb 27, 2010 11:17 am

OK, then just assemble the pieces. FullYear = (Century*100)+Year. Month and Day append a '0' when Len = 1. Cat together with your delimiter.

chulett · Post by **chulett** » Sat Feb 27, 2010 11:18 am

I was going to let them work out the gory details, but... there you go.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 27, 2010 2:49 pm

Nobody, not even the OP, has addressed how the time component has to be created.

chulett · Post by **chulett** » Sat Feb 27, 2010 3:05 pm

I specifically left it off as it seemed to be optional and since there seemed to be no actual time component incoming...