Post reply

convert date

  • October 24, 2011 at 4:11 pm

    #259159

    I need to convert yesterday's date from getdate()-1 to something like 20111023.

    I do it this way:

    Select CONVERT(VARCHAR(4),DATEPART(yyyy,GETDATE()))

    +

    CONVERT(VARCHAR(2),DATEPART(mm,GETDATE()))

    +

    CONVERT(VARCHAR(2), DATEPART(dd,GETDATE()-1))

    Is this correct or any better way of doing it.

    Thanks

  • October 24, 2011 at 4:26 pm

    #1398918

    This might work for you

    SELECT CONVERT(VARCHAR(8),GETDATE()-1,112)

    Result:

    20111023

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • October 24, 2011 at 4:29 pm

    #1398919

    Yes, thanks, much easier than what I have.

    What was in my mind was datepart, so it come up to a rather complicated one.

  • October 24, 2011 at 4:37 pm

    #1398921

    sqlfriends (10/24/2011)

    I need to convert yesterday's date from getdate()-1 to something like 20111023.

    I have to ask "Why?"... What are you going to do with it after you do the conversion? The reason I ask is because people seem to get themselves into a huge heap o' trouble by storing such formatted dates. There are actually very, very few reasons to do such a conversion in SQL Server. What's your's?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2011 at 4:40 pm

    #1398923

    I'm query jobhistory table in msdb database. It stored the run_date as this.

    I'm trying to create a query that to check if a job run or not yesterday.

  • October 24, 2011 at 6:33 pm

    #1398945

    sqlfriends (10/24/2011)

    I'm query jobhistory table in msdb database. It stored the run_date as this.

    I'm trying to create a query that to check if a job run or not yesterday.

    Ah! Understood! That "date" entry in the sysjobhistory table is actually stored as an INT. I suspect that the content of the table isn't so large as to need performance in particular but here's a lesson anyway... conversion of dates to VARCHAR, as would be done with the CONVERT examples given, is expensive in and of itself. Then when you compare the "date" VARCHAR to the integers in the table, an implicit conversion will happen... In this case, it's not so bad because INT has a higher data precedence than VARCHAR so the whole "date" column in the table won't be implicitly converted.

    On large tables, such a "data type mismatch" could make a huge difference especially if the content of the column in the table where of a lower precedence. A classic example of this mistake is where someone uses WHERE somebitcolumn = 1... the somebitcolumn would all be converted to an INT to match the "1" datatype which an INT by default.

    So like I said, the CONVERT examples given are likely good enough, in this case, although I believe the following would be faster both in the conversion and in use...

    SELECT DATEPART(yy,GETDATE()-1)*10000

    + DATEPART(mm,GETDATE()-1)*100

    + DATEPART(dd,GETDATE()-1) AS SomeDate

    At the very least, the storage of the resulting INT's will take less room than the VARCHARs. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2011 at 7:14 pm

    #1398948

    Thanks, it's very helpful

  • October 24, 2011 at 11:48 pm

    #1398967

    CELKO (10/24/2011)

    >> I need to convert yesterday's date from getdate()-1 to something like 20111023. <<

    You do not understand that DATE is a temporal data type; there is no converting it. Do you also think that INTEGER are stored as text strings? The display format for the NUMBER five can be '5' or 'V' or Chinese. That has nothing to do with the ABSTRACT internal representation that is not exposed to you.

    You also have not gotten the message that T-SQL uses the ANSI/ISO CURRENT_TIMESTAMP and nto the 1970's Sybase getdate() any more.

    I do it this way:

    CAST (CURRENT_TIMESTAMP AS DATE) AS today

    will use the ISO-8601 format (“yyyy-mm-dd”) but uyou do not care. The to get yesterday,

    DATEADD(DAY, -1, CAST (CURRENT_TIMESTAMP AS DATE)) AS yesterday

    >> is this correct or any better way of doing it.<<

    This is just fine for `1950's COBOL and punch cards when dates were strings. It is the worst way in 2011 and SQL.

    Nice advice, unfortunately, as is it is worthless when attempting to query msdb.dbo.sysjobhistory. You really should take the time to find out what the OP is attempting to do before offering advice.

    Instead of mindless insults about how things should be done in your perfect world, how about offering advice that actually helps someone working with MS SQL Server?

  • October 25, 2011 at 7:14 am

    #1399089

    CELKO (10/24/2011)

    >> I need to convert yesterday's date from getdate()-1 to something like 20111023. <<

    You do not understand that DATE is a temporal data type; there is no converting it. Do you also think that INTEGER are stored as text strings? The display format for the NUMBER five can be '5' or 'V' or Chinese. That has nothing to do with the ABSTRACT internal representation that is not exposed to you.

    ....

    WHOA! Easy there, big guy! Unlike most design problems, there's no chance of the OP fixing this one because he's interrogating a Microsoft designed table from the MSDB database where the date is stored as a {gasp!}... INTEGER! It's not the OPs fault this time. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 25, 2011 at 7:19 am

    #1399093

    sqlfriends (10/24/2011)

    Thanks, it's very helpful

    You bet. Thank you for the feedback.

    Shifting gears a bit, don't worry about Celko's rant. In most cases, his rant would be correct (although it shouldn't be in the form of a personal attack) about dates but it doesn't apply in your situation because of the nature of the sysjobhistory table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

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