DTS date function

#1

I have to export date data to a text file for use on a UNIX server. The
programmer is asking for the date field to be split up into two fields.

YYYYMMDD and
HHMMSS

If I convert the datetime filed to a string using eithier of the MONTH, DAY,
YEAR or DATEPART function it drops the fill zero on numbers less than 10.

Does a function exist that will not drop the fill zero on months, days or
time segments less than 10?

#2

MONTH, DAY, YEAR and DATEPART return an integer value, that doesn't have
leading zeros.
Try to convert this value into a string ...
something like this: "right('0' + convert(char(2), month(DateField)) ,2)"
should function.
Then you can concatenate the values and eventually convert all into a
numeric value, if necessary.

Francesco Anti

"Gary" <Gary (AT) discussions (DOT) microsoft.com> wrote

Quote:

I have to export date data to a text file for use on a UNIX server. The
programmer is asking for the date field to be split up into two fields.

YYYYMMDD and
HHMMSS

If I convert the datetime filed to a string using eithier of the MONTH,
DAY,
YEAR or DATEPART function it drops the fill zero on numbers less than 10.

Does a function exist that will not drop the fill zero on months, days or
time segments less than 10?

#3

Add an RTRIM before the RIGHT and it's perfect!

Thanks Francesco.

"Francesco Anti" wrote:

Quote:

MONTH, DAY, YEAR and DATEPART return an integer value, that doesn't have
leading zeros.
Try to convert this value into a string ...
something like this: "right('0' + convert(char(2), month(DateField)) ,2)"
should function.
Then you can concatenate the values and eventually convert all into a
numeric value, if necessary.

Francesco Anti

"Gary" <Gary (AT) discussions (DOT) microsoft.com> wrote in message
news:266B9133-024E-46E5-AAE4-5D7A55C15026 (AT) microsoft (DOT) com...
I have to export date data to a text file for use on a UNIX server. The
programmer is asking for the date field to be split up into two fields.

YYYYMMDD and
HHMMSS

If I convert the datetime filed to a string using eithier of the MONTH,
DAY,
YEAR or DATEPART function it drops the fill zero on numbers less than 10.

Does a function exist that will not drop the fill zero on months, days or
time segments less than 10?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS date function

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS date function - 01-21-2005 , 10:09 AM

Re: DTS date function - 01-21-2005 , 10:29 AM

Re: DTS date function - 01-21-2005 , 10:47 AM