Post reply

Casting Date/Time as string, padding empty chars with 0.

  • August 18, 2004 at 1:16 pm

    #86325

    Hi,

    I need to retrieve the time part of a Date/Time field in SQL Server 2000 as a 9-byte string.  This is what it needs to exactly look like:  hhmmssmss.

    This is the statement I am writing in order to retrieve this:

    CAST((DATEPART(hh,DATETIMEVAL) as char(2)) +

    CAST((DATEPART(mi,DATETIMEVAL) as char(2)) +

    CAST((DATEPART(ss,DATETIMEVAL) as char(2)) +

    CAST((DATEPART(mi,DATETIMEVAL) as char(3))

    That gives me something like what I want, but when the return values are in single digits ( for example, 06 hrs, 000 ms), it is only giving me 6 for hours and 0 for ms, and leaving the other chars blank.  For example, when I want 162205003, I am getting something like 1622 5  3.

    Bottom line:

    How do I either pad the chars with zeros when the values are smaller?

  • August 18, 2004 at 4:54 pm

    #519662

    Try

    Select Stuff(Stuff(Replace(Replace(Convert(Char(23),GetDate(),126),'-',''),':',''),9,1,''),15,1,'')

    Select Stuff(Stuff(Replace(Replace(Convert(Char(23),Cast('1 jan 1990 13:14:15.123' as datetime),126),'-',''),':',''),9,1,''),15,1,'')

  • August 18, 2004 at 6:26 pm

    #519675

    Hi Val

    It seems that your original statement is nearly giving you what you want, except that you have spaces where you want zeroes to appear. So can you just use

    replace([expression], ' ', '0')

    to fix it up?

    Regards

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 18, 2004 at 9:16 pm

    #519685

    Try select convert(varchar(9),getdate(),114)

  • August 18, 2004 at 9:24 pm

    #519686

    Anne's nearly got it.

    Try this:

    select replace(convert(varchar(12),getdate(),114),':','')

    Regards

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 19, 2004 at 2:24 am

    #519709

    Doesn't this just show up a weakness of SQL Server. Compared to Oracle's ToDate or VB's Format, this is all a bit complex for a pretty common task. I know, I know, use a function.

    Bill

    NB I can put more than 2 heresies in a single post: Select * is cool, primary keys are for retentives only and comments are for losers

  • August 19, 2004 at 3:19 am

    #519717

    Tell you what Bill, you write a T-SQL UDF that implements VB's Format function, and I'm sure you will get a special prize

  • August 19, 2004 at 11:15 am

    #519854

    You can use Replicate function. Look into BOL

    Leah Kats

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply