Select all Open in new windowfor those wishing to make their own table, here's how I derived mine.ĭeclare as datetime set = ' 04:05:06:007' select starts, delimited, case when starts = '. SQL 2012? Some new Format sample can be found hereĭIY Table: DELIMITED STARTS PATTERN STYLED DATE SYNTAX STYLE LENGTH YYYY YYYY MM DD 20010223 convert(varchar, your_data_here ,112) 112 8 YY YY MM DD 010223 convert(varchar, your_data_here ,12) 12 6 slash YYYY YYYY MM DD 3 convert(varchar, your_data_here ,111) 111 10 slash YY YY MM DD 01/02/23 convert(varchar, your_data_here ,11) 11 8 slash MM MM DD YYYY convert(varchar, your_data_here ,101) 101 10 slash MM MM DD YY 02/23/01 convert(varchar, your_data_here ,1) 1 8 slash DD DD MM YYYY convert(varchar, your_data_here ,103) 103 10 slash DD DD MM YY 23/02/01 convert(varchar, your_data_here ,3) 3 8 dot YYYY YYYY MM DD 2001.02.23 convert(varchar, your_data_here ,102) 102 10 dot YY YY MM DD 01.02.23 convert(varchar, your_data_here ,2) 2 8 dot DD DD MM YYYY convert(varchar, your_data_here ,104) 104 10 dot DD DD MM YY 23.02.01 convert(varchar, your_data_here ,4) 4 8 dash YYYY YYYY MM DD 04:05:06.007 convert(varchar, your_data_here ,121) 121 23 dash YYYY YYYY MM DD 04:05:06 convert(varchar, your_data_here ,120) 120 19 dash MM MM DD YYYY 02-23-2001 convert(varchar, your_data_here ,110) 110 10 dash MM MM DD YY 02-23-01 convert(varchar, your_data_here ,10) 10 8 dash DD DD MM YYYY 23-02-2001 convert(varchar, your_data_here ,105) 105 10 dash DD DD MM YY 23-02-01 convert(varchar, your_data_here ,5) 5 8 space MMM MMM DD YYYY 4:05:06:007AM convert(varchar, your_data_here ,9) 9 26 space MMM MMM DD YYYY 4:05:06:007AM convert(varchar, your_data_here ,109) 109 26 space MMM MMM DD YYYY 4:05AM convert(varchar, your_data_here ,100) 100 19 space MMM MMM DD YYYY convert(varchar, your_data_here ,107) 107 12 space MMM MMM DD YY Feb 23, 01 convert(varchar, your_data_here ,7) 7 10 space DD DD MMM YYYY 04:05:06:007 convert(varchar, your_data_here ,13) 13 24 space DD DD MMM YYYY 04:05:06:007 convert(varchar, your_data_here ,113) 113 24 space DD DD MM YYYY convert(varchar, your_data_here ,106) 106 11 space DD DD MM YY 23 Feb 01 convert(varchar, your_data_here ,6) 6 9 colon hh hh:mm:ss:ms 04:05:06:007 convert(varchar, your_data_here ,14) 14 12 colon hh hh:mm:ss:ms 04:05:06:007 convert(varchar, your_data_here ,114) 114 12 colon hh hh:mm:ss 04:05:06 convert(varchar, your_data_here ,8) 8 8 colon hh hh:mm:ss 04:05:06 convert(varchar, your_data_here ,108) 108 8 Convert(datetime, your_data_here ,127) - if there is time zone info ![]() ![]() /gets the time only (date portion is '' and is considered the "0 time" of dates in MSSQL, even with the datatype min value of. SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)) /this has been especially useful for JOINS /gets the date only, 20x faster than using Convert/Cast to varchar /This contains common date functions for MSSQL server They are numerical (a float) and performance will suffer from those data type conversions.ĭig these handy conversions I have compiled over the years. Try not to use any Character / String based operations if possible when working with dates. ![]() Select DATENAME(day, + SUBSTRING(UPPER(DATENAME(month, 0,4) Use DATENAME and wrap the logic in a Function, not a Stored Proc declare as DateTime
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |