«
»
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

Alter Function [dbo].[fn_format_date] (@Datetime DateTime, @FormatMask NVarChar(32))
Returns NvarChar(32)
As
Begin
    Declare @StringDate NVarChar(32)
    Set @StringDate = @FormatMask
 
    If (CharIndex (N'YYYY',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'YYYY', DateName(YY, @Datetime))
    End
    If (CharIndex (N'YY',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'YY', Right(DateName(YY, @Datetime),2))
	End  
    If (CharIndex (N'MM',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'MM', Right(N'0'+Convert(VARCHAR,DatePart(MM, @Datetime)),2))
	End
    If (CharIndex (N'M',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'M', Convert(VARCHAR,DatePart(MM, @Datetime)))
	End
    If (CharIndex (N'DD',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'DD', Right(N'0'+DateName(DD, @Datetime),2))
	End
    If (CharIndex (N'D',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'D', DateName(DD, @Datetime))
    End
Return @StringDate
End
 
Go 
Set Nocount On
Declare @lDateTime Datetime = '20110506'
Print [dbo].[fn_format_date] (@lDateTime, 'MM/DD/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'DD/MM/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'M/DD/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'M/D/YYYY') 
Print [dbo].[fn_format_date] (@lDateTime, 'M/D/YY')
Print [dbo].[fn_format_date] (@lDateTime, 'MM/DD/YY')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYY/MM/DD')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYYMMDD')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYY-MM-DD')
 
/*
05/06/2011
06/05/2011
5/06/2011
5/6/2011
5/6/11
05/06/11
2011/05/06
20110506
2011-05-06
*/
Share

This entry was posted by Jan Novak on December 12, 2011 at 19:25:12 under MS SQL Server, T-SQL. You can leave a response, or trackback from your own site. Follow any responses to this entry through the RSS 2.0 feed.

Leave a Reply