Post reply

Today''s Date

  • January 9, 2007 at 1:44 pm

    #115651

    I need code the SQL code to generate today's date

    'MMDDYYHHMMSS' with no Semi colon how should I do this??

    so my output should look like

    '010907013455'

    (The above result isToday's date and 1 hr 34 min and 55 sec)

     

    Thanks

    Nita

  • January 9, 2007 at 1:49 pm

    #682176

    REPLACE(CONVERT(varchar(10), @Datetime, 1), '/', '') +

    REPLACE(CONVERT(varchar(10), @Datetime, 8), ':', '')

    _____________
    Code for TallyGenerator

  • January 9, 2007 at 1:52 pm

    #682178

    You could use DatePart() and construct the string from each date element - would require the zero-padding logic.

    An alternative is using format codes 10 (date as DD-MM-YY) and 8 (time as HH:MM:SS) and using Replace() to remove the ':' and '-' delimiters:

    Select

      Replace(

        Replace(

          convert(varchar, getdate(), 10) + convert(varchar, getdate(), 8),

          '-', ''),

        ':', '')

  • January 9, 2007 at 1:54 pm

    #682179

    are you sure you need SQL to return the date in that format? can you do it clietn side instead?

    Programming languages like vb6/.NET, delphi, etc all can handle formatting a datetime field natively better than SQL.

    ie vb6: Format(Now,"MMDDYYYYHHMMNn")

    also, you are not going to store the date this way in the database, right? always use a datetime field and not a varchar, of course.

    here's an example of how to get the slices you want, but it's kinda clunky:

    DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT 
     + RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) 
     + RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) 
     + CAST(DATEPART(YYYY,@DT) AS CHAR(4)) +  
     + CASE WHEN DATEPART(HH,@DT) < 13 
            THEN RIGHT(CAST(100+DATEPART(HH,@DT) AS CHAR(3)),2) 
            ELSE CAST(DATEPART(HH,@DT)-12 AS CHAR(2)) 
            END 
     + RIGHT(CAST(100+DATEPART(MI,@DT) AS CHAR(3)),2)     
     + CASE WHEN DATEPART(HH,@DT) < 13
            THEN 'AM'
            ELSE 'PM'
            END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 9, 2007 at 1:56 pm

    #682180

    Owesome,

    Thanks for the help

    Nita

     

  • January 9, 2007 at 2:11 pm

    #682183

    You welcome.

    Next time try to read "Cast and Convert" topic in BOL by yourself.

    _____________
    Code for TallyGenerator

Viewing 6 posts - 1 through 5 (of 5 total)

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