Monday, March 19, 2012

Formatting Date as integer

I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer

I have used the following code (not the conversion function as I don’t need Hyphen '-')

for YYYYDDMM

SET @.DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) ))

& for MMDDYY

SET @.DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+

CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) +

SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) ))

I am getting the result

YYYYMMDD= 200688

MMDDYY =8806

but i want result in

YYYYDDMM = 20060808

MMDDYY = 080806

note: I need to convert in integer, finally, caz database data type is integer.

can any one give me solution

waiting for quick reply

regards,

Anas

Just use convert:

select cast(convert(varchar(8),getdate(),112) as integer)

I use this for our date_dimension/calendar table surrogate keys all of the time.

|||thanx for your quick reply.

No comments:

Post a Comment