Consulta de MySQL a SQL Server

[Reynier]

Hola a todos:
La cosa es que tengo la siguiente consulta formada en MySQL:

Código PHP:

  select *, dayofmonth(fecha_publicacion) as dia, dayofweek(fecha_publicacion) as semana, month(fecha_publicacion) as mes, year(fecha_publicacion) as year, date_format(fecha_publicacion,'%h:%i %p') as hora from jcgrm_noticias where importancia = 10 and visible<>0 order by RAND() 
   

ahora he intentado probarla con MSSQL y me he dado cuenta de que el SQL no es tan estándar como me habían dicho. He realizado estos cambios:

Código PHP:

   select *, day(fecha_publicacion) as dia, month(fecha_publicacion) as mes, year(fecha_publicacion) as year from jcgrm_noticias where importancia = 10 and visible<>0 order by newID() 
   

pero como obtengo el dia de la semana? (si es que se puede, claro está) y la hora en formato hh:mm:ss am o pm. El campo es datetime por lo que me imagino que al igual que en MySQL se puedan obtener esos datos.

Salu2
PD: Donde puedo descargar un buen manual de SQL donde vengan todas esas funciones que le hacen falta a uno de vez en cuando?

[Reynier]

Chicos:
Ya he resuelto una parte de mi problema. Ahora me quedan otras dudas. está es la consulta actual:

Código:

select top 1 DATEPART(dd,fecha_publicacion) as dia, DATEPART(mm,fecha_publicacion) as mes, DATEPART(ss,fecha_publicacion) as semana, DATEPART(yyyy,fecha_publicacion) as year, DATEPART(hh,fecha_publicacion) as hora, DATEPART(mi,fecha_publicacion) as minutos, DATEPART(ss,fecha_publicacion) as segundos from jcgrm_noticias where importancia = 10 and visible<>0 order by newid()

pero ahora necesito escoger el día de la semana o sea de 1 a 7 o 0 a 6 no se como sea en este caso y lo otro que necesito es si la hora fue AM o PM. He probado el DATEPART asi para la semana y me da este error:

Código:

DATEPART(ds,fecha_publicacion) as semana

pero me da este error

Server: Msg 155, Level 15, State 1, Line 1
'ds' is not a recognized datepart option.

que hago entonces ?

Salu2

[Reynier]

Miren ustedes que esto me ha hecho estudiar y he encontrado muchas cosas. Ya he resuelto el problema de la semana. era de esta forma:

Código:

DATEPART(dw,fecha_publicacion) as semana

pues está en ingles. Lo que no he resuelto y tampoco he hallado es el problema del AM o el PM y ademas como poner la fecha en formato 1 - 12 horas porque asi de esa forma que lo tengo me lo toma como 1 - 24 horas.

Salu2

[Atzeneta]

Ese Problema puedes solucionarlo con programación o dentro de la misma consulta. A ti te da igual como lo guarde, lo que quieres es visualizarlo de una u otra forma.

Créate una tabla de correspondencia de horas como podría ser:

tblHoras: IdHora(1, 2 , 3... Hasta 24), Conv12 (Numérico), AMPM (Texto - Tamaño 2)

Ej:
Registro 1: IdHora=1; Conv12=1; AMPM='AM'
Registro 2: IdHora=2; Conv12=2; AMPM='AM'
Registro 3: IdHora=3; Conv12=3; AMPM='AM'
....
Registro 13: IdHora=13; Conv12=1; AMPM='PM'
....
Registro 23: IdHora=23; Conv12=11; AMPM='PM'
Registro 24: IdHora=24; Conv12=12; AMPM='PM'

Crea la consulta:

SELECT *, Conv12 + ':' + Datepart(n, tbl.Hora) + ':' + DatePart(s, tbl.Hora) + ' ' + AMPM AS Hora12 FROM tbl INNER JOIN tblHoras ON DatePart(hh, tbl.Hora)=tblHoras.IdHora

A partir de aquí, en el Campo Hora12 tendrás la información que deseas.

Según la Base de Datos que estés utilizando, lo único que cambiará serán las funciones utilizadas para desglosar fechas y la forma de concatenar campos, pero la sistemática sería ésta.

[Mithrandir]

Ejecuta esta sentencia en el Query Analyzer

Código:

print convert(nvarchar, getdate(), 100)

La clave aqui es jugar con el valor pasado a CONVERT (100 en el ejemplo)

Reivsa la ayuda del comando convert para que veas la lista de numeros que puedes pasar

[Reynier]

Estuve revisando sobre lo que me dijiste y va bien, pero ahora todas las opciones posibles que hay me devuelven resultados como estos:

- 0 o 100 (*) Predeterminado mon dd yyyy hh:miAM (o PM)
1 101 EE.UU. mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 Británico/Francés dd/mm/yy
4 104 Alemán dd.mm.yy
5 105 Italiano dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 o 109 (*) Predeterminado + milisegundos mon dd yyyy hh:mi:ss:mmmAM (o PM)
10 110 EE.UU. mm-dd-yy
11 111 JAPÓN yy/mm/dd
12 112 ISO yymmdd
- 13 o 113 (*) Europeo predeterminado + milisegundos dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 o 120 (*) ODBC canónico yyyy-mm-dd hh:mi:ss(24h)
- 21 o 121 (*) ODBC canónico (con milisegundos) yyyy-mm-dd hh:mi:ss.mmm(24h)
* Los valores predeterminados (estilo 0 o 100, 9 o 109, 13 o 113, 20 o 120, y 21 o 121) siempre devuelven el siglo (yyyy).
** Entrada cuando se convierte a datetime; Salida cuando se convierte a cadenas de caracteres.

y lo que necesito saber es el AM o PM solo, o la hora con AM y PM solos. Existirá alguna manera de hacerlo así?

Salu2

[Atzeneta]

Si estás utilizando sqlSERVER te puede servir esta forma de hacero, la anterior la podías utilizar para cualquier base de datos:

AM ó PM: RIGHT(CONVERT(nvarchar, Hora), 2) AS AMPM
Hora (formato 1 - 12): REPLACE(LEFT(RIGHT(CONVERT(nvarchar, Hora), 7), 5), ' ', '') AS Hora_12
Hora (formato 1 - 12) con cinco dígitos siempre: CASE WHEN LEN(REPLACE(LEFT(RIGHT(CONVERT(nvarchar, Hora), 7), 5), ' ', '')) = 5 THEN REPLACE(LEFT(RIGHT(CONVERT(nvarchar, Hora), 7), 5), ' ', '') ELSE '0' + REPLACE(LEFT(RIGHT(CONVERT(nvarchar, Hora), 7), 5), ' ', '') END AS Hora_12

[Reynier]

Gracias. Ya me funcionó correctamente las cosas y como quería.

Salu2

[Mithrandir]

Sería interesante que digas como.

Asi ayudas a quien venga detras de ti

[Reynier]

Así es como me quedo la consulta dedspués de tantos problemas.

SELECT TOP 1 *, DATEPART(dd,fecha_publicacion) as dia, DATEPART(mm,fecha_publicacion) as mes, DATEPART(dw,fecha_publicacion) as semana, DATEPART(yyyy,fecha_publicacion) as year, RIGHT(CONVERT(nvarchar, fecha_publicacion), 2) AS modo, REPLACE(LEFT(RIGHT(CONVERT(nvarchar, fecha_publicacion), 7), 5), ' ', '') AS hora FROM jcgrm_noticias WHERE importancia = 10 and visible<>0 order by newid()

Salu2

[Mithrandir]

Perfecto, thx