¿Cómo obtener la hora desde el formato DateTime en SQL?

Question

¿Cómo obtener la hora desde el formato DateTime en SQL?

Quiero obtener solo el tiempo de la columna DateTime usando la consulta SQL usando SQL Server 2005 y 2008 Salida predeterminada:

AttDate                   
==
2011-02-09 13:09:00    
2011-02-09 14:10:00

Me gustaría esta salida:

AttDate                Time 
==
2011-02-09 13:09:00    13:09
2011-02-09 14:10:00    14:10

133

datetime sql-server tsql sql-server-2005

Author: iconoclast, 2011-10-10

Source

14 answers

Asumiendo Sql server

SELECT CONVERT(VARCHAR(8),GETDATE(),108)

25

Author: V4Vendetta,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-10 09:00:36

SQL Server 2008 + tiene un tipo de datos" time "

SELECT 
    ..., CAST(MyDateTimeCol AS time)
FROM
   ...

Para versiones anteriores, sin conversiones varchar

SELECT 
    ..., DATEADD(dd, DATEDIFF(dd, MyDateTimeCol, 0), MyDateTimeCol)
FROM
   ...

16

Author: gbn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-10 08:55:35

La forma más sencilla de obtener el tiempo de datetime sin pila de milisegundos es:

SELECT convert(time(0),getDate())

7

Author: BigDaddy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-06-22 12:18:43

Intenta usar esto

Fecha a hora
```
select cast(getdate() as time(0))
```
Tiempo para TinyTime
```
select cast(orig_time as time(0))
```

5

Author: Cantarero,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-24 18:24:35

Prueba esto:

select  convert(nvarchar,CAST(getdate()as time),100)

3

Author: Balaji N,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-06 20:36:39

Prueba esto, funcionará:

CONVERT(VARCHAR(8),DATETIME,114)

Para su referencia .

3

Author: user8498521,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-22 06:58:17

select AttDate,convert(char(5), AttDate, 108) [Time] from yourTableName

2

Author: sagar Shah,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-10 18:27:00

A menudo uso este script para obtener Tiempo de DateTime:

SELECT CONVERT(VARCHAR(9),RIGHT(YOURCOLUMN_DATETIME,9),108) FROM YOURTABLE

2

Author: ChinoNoypi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-04 07:53:34

Para obtener la hora de datetime, podemos usar

SELECT CONVERT(VARCHAR(20), GETDATE(), 114)

1

Author: thevan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-15 09:36:24

Si quieres salir con algo de este estilo: Oct 23 2013 10: 30AM

Use esto

SELECT CONVERT(NVARCHAR(30),getdate(), 100)

convert() el método toma 3 parámetros

tipo de datos
Columna / Valor
Estilo: Los estilos disponibles son de 100 a 114. Puede elegir dentro del rango de. Elija uno por uno para cambiar el formato de fecha.

1

Author: Arif Ansari,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-06 19:06:32

select cast (as time(0))

Sería una buena cláusula. Por ejemplo:

(select cast(start_date as time(0))) AS 'START TIME'

0

Author: Metin Özsoy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-04 07:53:18

Obtener la fecha del servidor

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) FROM TABLENAME WHERE ...

O

Si se almacena en la tabla

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), datename, 100), 7)) FROM TABLENAME WHERE ...

Resultado:

11: 41AM

0

Author: Luis Gerardo López Salazar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-06 16:45:05

select substr(to_char(colUmn_name, 'DD/MM/RRRR HH:MM:SS'),11,19) from table_name;

Salida: de

05:11:26
05:11:24
05:11:24

-2

Author: user7131338,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-08 11:36:00

score 261 · Accepted Answer

SQL Server 2008:

select cast(AttDate as time) [time]
from yourtable

Versiones anteriores:

select convert(char(5), AttDate, 108) [time]
from yourtable

261

Author: t-clausen.dk,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-11 10:09:12