SQL Server - fecha en sql

   
Vista:

fecha en sql

Publicado por alexis (11 intervenciones) el 02/06/2015 18:20:21
buenas tardes que tal, consulta si alguien tiene la dirección

de la tabla de conversion de entero a date o varchar a date en sql,

hace un tiempo vi esa informacion en google, pero ahora no la encuentro o palabra especifica para buscarla


ejemplo las que utilizan los comandos 102, 1010
etc
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Isaias

fecha en sql

Publicado por Isaias (3186 intervenciones) el 02/06/2015 19:34:30
Ya las había publicado

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
USE AdventureWorks
SET LANGUAGE us_english
-- Convert string to date using style (format) numbers - sql dates format
-- SQL convert text to date - SQL convert string to date / datetime
SELECT convert(datetime,'15/03/18',3) -- 2018-03-15 00:00:00.000
SELECT convert(datetime,'15.03.18',4) -- 2018-03-15 00:00:00.000
 
-- Convert datetime to text style (format) list - sql time format
-- SQL Server without century (YY) date styles (there are exceptions!)
-- Generally adding 100 to style number results in century format CCYY / YYYY
SELECT convert(varchar,getdate())    -- Mar 15 2018 10:35AM
SELECT convert(varchar,getdate(),0)  -- Mar 15 2018 10:35AM
SELECT convert(varchar,getdate(),1)  -- 03/15/18
SELECT convert(varchar,getdate(),2)  -- 18.03.15
SELECT convert(varchar,getdate(),3)  -- 15/03/18
SELECT convert(varchar,getdate(),4)  -- 15.03.18
SELECT convert(varchar,getdate(),5)  -- 15-03-18
SELECT convert(varchar,getdate(),6)  -- 15 Mar 18
SELECT convert(varchar,getdate(),7)  -- Mar 15, 18
SELECT convert(varchar,getdate(),8)  -- 10:39:39
SELECT convert(varchar,getdate(),9)  -- Mar 15 2018 10:39:48:373AM
SELECT convert(varchar,getdate(),10) -- 03-15-18
SELECT convert(varchar,getdate(),11) -- 18/03/15
SELECT convert(varchar,getdate(),15) -- 180315
SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590
SELECT convert(varchar,getdate(),14) -- 10:41:25:903
SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56
SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950
SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM
SELECT convert(varchar,getdate(),23) -- 2018-03-15
SELECT convert(varchar,getdate(),24) -- 10:45:45
SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263
 
-- T-SQL with century (YYYY or CCYY) datetime styles (formats)
SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016
SELECT convert(varchar, getdate(), 102) -- 2016.10.23
SELECT convert(varchar, getdate(), 103) -- 23/10/2016
SELECT convert(varchar, getdate(), 104) -- 23.10.2016
SELECT convert(varchar, getdate(), 105) -- 23-10-2016
SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016
SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016
SELECT convert(varchar, getdate(), 108) -- 09:10:34
SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)
SELECT convert(varchar, getdate(), 110) -- 10-23-2016
SELECT convert(varchar, getdate(), 111) -- 2016/10/23
SELECT convert(varchar, getdate(), 112) -- 20161023
SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383
SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)
SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)
SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383
SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383
GO
-- SQL cast string to datetime - time part 0 - sql hh mm
-- SQL Server cast string to DATE (SQL Server 2008 feature) - sql yyyy mm dd
SELECT [Date] =     CAST('20120228' AS date)         -- 2012-02-28
SELECT [Datetime] = CAST('20120228' AS datetime)      -- 2012-02-28 00:00:00.000
SELECT [Datetime] = CAST('20120228' AS smalldatetime) -- 2012-02-28 00:00:00
 
-- SQL convert string to datetime - time part 0
-- SQL Server convert string to date - sql times format
SELECT [Datetime] = CONVERT(datetime,'2010-02-28')
SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28')
 
SELECT [Datetime] = CAST('Mar 15, 2010' AS datetime)
SELECT [Datetime] = CAST('Mar 15, 2010' AS smalldatetime)
 
SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010')
SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010')
 
SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS datetime)
SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS smalldatetime)
 
SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010 12:07:34.444')
SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010 12:07:34.444')
 
SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS datetime)
SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS smalldatetime)
 
SELECT [Datetime] = CONVERT(datetime,'2010-02-28 12:07:34.444')
SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28 12:07:34.444')
 
-- Double conversion
SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS datetime)
SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS smalldatetime)
 
SELECT [Datetime] = CONVERT(datetime,convert(varchar,getdate()))
SELECT [Datetime] = CONVERT(smalldatetime,convert(varchar,getdate()))
------------
 
-- MSSQL convert date string to datetime - time is set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,'07-10-2016',110)        -- Jul 10 2016 12:00AM
PRINT CONVERT(datetime,'2016/07/10',111)        -- Jul 10 2016 12:00AM
PRINT CONVERT(varchar,CONVERT(datetime,'20160710',  112),121)
-- 2016-07-10 00:00:00.000
------------
 
-- Selected named date styles
DECLARE @DateTimeValue varchar(32)
 
-- US-Style
-- Convert string to datetime sql - sql convert string to datetime
SELECT @DateTimeValue = '10/23/2016'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
 
SELECT @DateTimeValue = '10/23/2016 23:01:05'
SELECT StringDate = @DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
 
-- UK-Style, British/French
SELECT @DateTimeValue = '23/10/16 23:01:05'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
 
SELECT @DateTimeValue = '23/10/2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
 
-- German-Style
SELECT @DateTimeValue = '23.10.16 23:01:05'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
 
 
SELECT @DateTimeValue = '23.10.2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
 
 
-- Double conversion to US-Style 107 with century: Oct 23, 2016
SET @DateTimeValue='10/23/16'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
 
-- SQL dateformat setting
USE AdventureWorks2008;
SELECT convert(datetime,'14/05/08')
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted 
in an out-of-range value.
*/
SET DATEFORMAT ymd
SELECT convert(datetime,'14/05/08')   -- 2014-05-08 00:00:00.000
-- Setting DATEFORMAT to UK-Style
SET DATEFORMAT dmy
SELECT convert(datetime,'20/05/14')   -- 2014-05-20 00:00:00.000
-- Setting DATEFORMAT to US-Style
SET DATEFORMAT mdy
SELECT convert(datetime,'05/20/14')   -- 2014-05-20 00:00:00.000
SELECT convert(datetime,'05/20/2014') -- 2014-05-20 00:00:00.000
GO
 
------------
-- SQL date & time eliminating dividing characters
------------
-- MSSQL replace string function
-- T-SQL string concatenate (+)
USE AdventureWorks2008;
SELECT replace(convert(VARCHAR(10),getdate(),102),'.','')
-- 20120315
SELECT replace(convert(VARCHAR(10),getdate(),111),'/','')
-- 20120315
-- SQL triple replace
SELECT replace(replace(replace(convert(VARCHAR(25),
       getdate(),20),'-',''), ':',''),' ','')
-- 20120529090427
-- T-SQL concatenating from a date and a time conversion
SELECT replace(convert(VARCHAR(10),getdate(),111),'/','') +
       replace(convert(VARCHAR(8),getdate(),108),':','')
-- 20120315085654
 
------------
-- Converting string dates from a table
------------
 
-- Create and populate a test table with a string date
USE tempdb;
SELECT
      DepartmentID,
      LastUpdate=CONVERT(varchar,
                 dateadd(day, DepartmentID, ModifiedDate),100)
INTO DeptInfo
FROM AdventureWorks.HumanResources.Department
 
SELECT * FROM DeptInfo
/* Partial results
 
DepartmentID      LastUpdate
1                       Jun  2 1998 12:00AM
2                       Jun  3 1998 12:00AM
*/
 
-- Convert string date column to datetime
SELECT
      DepartmentID,
      LastChangeDate=convert(datetime, LastUpdate)
FROM DeptInfo
/* Partial results
 
DepartmentID            LastChangeDate
1                       1998-06-02 00:00:00.000
2                       1998-06-03 00:00:00.000
*/
DROP TABLE DeptInfo
GO
The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!
 
-------------------------------------------------------
-- Casting string date & time together and separately
-------------------------------------------------------
-- SQL cast string to datetime
SELECT CAST('20100315 16:40:31' AS datetime)
-- Result: 2010-03-15 16:40:31.000
 
-- SQL cast string to date - time part 0
SELECT CAST('20100315' AS datetime)
-- Result: 2010-03-15 00:00:00.000
 
-- SQL cast string to time - date part 1900-01-01
SELECT CAST('16:40:31' AS smalldatetime)
-- Result: 1900-01-01 16:41:00
------------
-- SQL DATEDIFF with string date
------------
DECLARE @sDate varchar(10)
SET @sDate = '2010/03/15'
-- DATEDIFF (delta) between two dates in months
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), @sDate)
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CAST(@sDate as datetime))
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate))
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,111))
-- Seme results for above: 2008-12-29 11:04:51.097    15
 
-- SQL convert to datetime with wrong style (111 correct, 112 incorrect)
SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,112))
/* ERROR
 
Msg 241, Level 16, State 1, Line 11
Conversion failed when converting date and/or time from character string.
*/
------------
------------
-- SQL Server date string search guidelines - comparing dates
------------
-- Date equal search
DECLARE @Date1 datetime, @Date2 datetime, @Date3 datetime
SET @Date1 = '2012-01-01'
SET @Date2 = '2012-01-01 00:00:00.000'
SET @Date3 = '2012-01-01 11:00'
 
SELECT @Date1, @Date2, @Date3
-- Date-only @Date1 is translated to datetime
-- 2012-01-01 00:00:00.000    2012-01-01 00:00:00.000  2012-01-01 11:00:00.000
 
-- The following is a datetime comparison, not a date-only comparison
IF (@Date1 = @Date2) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'
-- EQUAL
 
-- Equal test fails because time parts are different
IF (@Date1 = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'
-- NOT EQUAL
-- The string date implicitly converted to datetime for the equal test
IF ('2012-01-01' = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'
-- NOT EQUAL
 
-- Safe way to search for a specific date
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE '2004/02/01' = CONVERT(varchar, OrderDate,111)
-- 244
 
-- Equivalent to
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2004/02/01  00:00:00.000' AND '2004/02/01 23:59:59.997'
-- 244
 
 
-- Safe way to search for a specific date range
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CONVERT(varchar, OrderDate,111) BETWEEN '2004/02/01' AND '2004/02/14'
-- 1059
 
-- Equivalent to
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2004/02/01 00:00:00.000' AND '2004/02/14 23:59:59.997'
-- 1059
SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
WHERE OrderDate >= '2004/02/01 00:00:00.000'
  AND OrderDate <  '2004/02/15 00:00:00.000'
-- 1059
------------
------------
-- SQL Server convert from string to smalldatetime
------------
-- T-SQL convert from format mm/dd/yyyy to smalldatetime
SELECT CONVERT(smalldatetime, '10/23/2016', 101)
-- 2016-10-23 00:00:00
-- MSSQL convert from format dd/mm/yyyy to smalldatetime
SELECT CONVERT(smalldatetime, '23/10/2016', 103)
-- 2016-10-23 00:00:00
-- Month 23 is out of range
SELECT CONVERT(smalldatetime, '23/10/2016', 101)
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted 
in an out-of-range value.
*/
------------
-- Translate/convert string/text hours and minutes to seconds
------------
DECLARE @TimeStr varchar(16) = '20:49:30'
SELECT   PARSENAME(REPLACE(@TimeStr,':','.'),1)
       + PARSENAME(REPLACE(@TimeStr,':','.'),2) * 60
       + PARSENAME(REPLACE(@TimeStr,':','.'),3) * 3600
-- 74970
------------
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

fecha en sql

Publicado por alexis (11 intervenciones) el 04/06/2015 20:50:42
muchas gracias
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

fecha en sql

Publicado por Isaias (3186 intervenciones) el 04/06/2015 22:05:47
De nada, Salu2
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar