1. SQL / Говнокод #18490

    −163

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    SELECT     top 20 *,
		CAST(DATEPART(yy, rs_message_sendtime) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, 
                      rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(m, 
                      rs_message_sendtime) AS varchar(4)) END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)))) 
                      = 1 THEN '0' + CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)) END) 
                      + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_sendtime) 
                      AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_sendtime) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, 
                      rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(mi, 
                      rs_message_sendtime) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4))) 
                      = 1 THEN '0' + CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)) END) 
                      AS sendtime, 
					  CAST(DATEPART(yy, rs_message_valid_from) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, rs_message_valid_from) 
                      AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(m, rs_message_valid_from) 
                      AS varchar(4)) END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_valid_from) AS varchar(4)))) = 1 THEN '0' + CAST(DATEPART(d, 
                      rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_valid_from) AS varchar(4)) END) 
                      + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_valid_from) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_valid_from) 
                      AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_valid_from) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, 
                      rs_message_valid_from) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(mi, 
                      rs_message_valid_from) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4))) 
                      = 1 THEN '0' + CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)) END) 
                      AS validfrom, 
					  CAST(DATEPART(yy, rs_message_valid_to) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, rs_message_valid_to) 
                      AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(m, rs_message_valid_to) AS varchar(4)) 
                      END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_valid_to) AS varchar(4)))) = 1 THEN '0' + CAST(DATEPART(d, rs_message_valid_to) 
                      AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_valid_to) AS varchar(4)) END) + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_valid_to) 
                      AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_valid_to) 
                      AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, rs_message_valid_to) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, 
                      rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(mi, rs_message_valid_to) AS varchar(4)) END) 
                      + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_valid_to) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(ss, rs_message_valid_to) 
                      AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_valid_to) AS varchar(4)) END) AS validto
					  
FROM         rs_message_outbox_ready INNER JOIN
                      rs_customer_feed ON rs_customer_feed.rs_customer_feed_id = rs_message_outbox_ready.rs_customer_feed_id INNER JOIN
                      rs_customer ON rs_message_outbox_ready.rs_customer_id = rs_customer.rs_customer_id
	WHERE     
	(rs_message_outbox_ready.rs_message_sendtime <= GETDATE()) AND
	(rs_message_outbox_ready.rs_message_type = 2) AND 
	(rs_customer_feed.rs_customer_feed_status = 1) AND 
	(rs_customer.rs_customer_status = 1) AND 
	(rs_customer_feed.rs_customer_feed_category = 2) AND 
	(rs_message_outbox_ready.rs_message_status = 5) AND
	(rs_customer_feed.rs_customer_feed_xml=1)

    Переводим дату из одного формата в другой :)

    Запостил: kropotor, 17 Июля 2015

    Комментарии (0) RSS

    Добавить комментарий

    Ошибка компиляции комментария:
    1. Гости могут высказаться только во вторник, пятницу или субботу
    ava Я, guest, находясь в здравом уме и твердой памяти, торжественно заявляю:
    А не использовать ли нам bbcode?
    • [b]жирный[/b] — жирный
    • [i]курсив[/i] — курсив
    • [u]подчеркнутый[/u] — подчеркнутый
    • [s]перечеркнутый[/s] — перечеркнутый
    • [blink]мигающий[/blink] — мигающий
    • [color=red]цвет[/color] — цвет (подробнее)
    • [size=20]размер[/size] — размер (подробнее)
    • [code=<language>]some code[/code] (подробнее)
    Проверочный код