Tengo una fecha en la columna donde se muestra la fecha en el formato 2009-11-18 10:55:28.370.

Sólo quiero llegar a la fecha (tiempo) de ese valor. ¿Cómo puedo hacer eso?

OriginalEl autor thegunner | 2010-04-13

2 Comentarios

  1. 8

    Si está utilizando SQL Server 2008, ahora hay un tipo de datos de FECHA. Hace que sea mucho más natural!

    SELECT CONVERT(Date, GETDATE())
    ok respuesta es: seleccione creation_date de WEB_RISK_RISK donde CREATION_DATE = convertir(fecha,GETDATE()) – gracias por la sugerencia.

    OriginalEl autor AdaTheDev

  2. 3

    se llama «en el piso de un datetime», hacer como este para quitar solo el tiempo (este es el método más rápido, más rápido que el uso de CONVERT() o CAST() picadura de formato):

    DECLARE @datetime datetime;
    SET @datetime = '2008-09-17 12:56:53.430';
    SELECT DATEADD(day,DATEDIFF(day,0,@datetime),0)

    De SALIDA:

    -----------------------
    2008-09-17 00:00:00.000
    
    (1 row(s) affected)

    aquí es cómo hacerlo para otras partes de un datetime:

    --Floor a datetime
    DECLARE @datetime datetime;
    SET @datetime = '2008-09-17 12:56:53.430';
    
    SELECT '0 None',  @datetime                                                                   -- none    2008-09-17 12:56:53.430
    UNION SELECT '1 Second',DATEADD(second,DATEDIFF(second,'2000-01-01',@datetime),'2000-01-01')  -- Second: 2008-09-17 12:56:53.000
    UNION SELECT '2 Minute',DATEADD(minute,DATEDIFF(minute,0,@datetime),0)                        -- Minute: 2008-09-17 12:56:00.000
    UNION SELECT '3 Hour',  DATEADD(hour,DATEDIFF(hour,0,@datetime),0)                            -- Hour:   2008-09-17 12:00:00.000
    UNION SELECT '4 Day',   DATEADD(day,DATEDIFF(day,0,@datetime),0)                              -- Day:    2008-09-17 00:00:00.000
    UNION SELECT '5 Month', DATEADD(month,DATEDIFF(month,0,@datetime),0)                          -- Month:  2008-09-01 00:00:00.000
    UNION SELECT '6 Year',  DATEADD(year,DATEDIFF(year,0,@datetime),0)                            -- Year:   2008-01-01 00:00:00.000
    ORDER BY 1
    PRINT' '
    PRINT 'Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor'
    PRINT 'this always uses a date less than the given date, so there will be no arithmetic overflow'
    SELECT '1 Second',DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,@datetime),0)-1,@datetime),DATEADD(day,DATEDIFF(day,0,@datetime),0)-1)  -- Second: 2008-09-17 12:56:53.000

    De SALIDA:

    -------- -----------------------
    0 None   2008-09-17 12:56:53.430
    1 Second 2008-09-17 12:56:53.000
    2 Minute 2008-09-17 12:56:00.000
    3 Hour   2008-09-17 12:00:00.000
    4 Day    2008-09-17 00:00:00.000
    5 Month  2008-09-01 00:00:00.000
    6 Year   2008-01-01 00:00:00.000
    
    (7 row(s) affected)
    
    
    Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor
    this always uses a date less than the given date, so there will be no arithmetic overflow
    
    -------- -----------------------
    1 Second 2008-09-17 12:56:53.000
    
    (1 row(s) affected)

    OriginalEl autor KM.

Dejar respuesta

Please enter your comment!
Please enter your name here