Skip to main content

Date Formats

DATE FORMATS

Format # Query (current date: 12/30/2006) Sample

1 select convert(varchar, getdate(), 1) 12/30/06

2 select convert(varchar, getdate(), 2) 06.12.30

3 select convert(varchar, getdate(), 3) 30/12/06

4 select convert(varchar, getdate(), 4) 30.12.06

5 select convert(varchar, getdate(), 5) 30-12-06

6 select convert(varchar, getdate(), 6) 30 Dec 06

7 select convert(varchar, getdate(), 7) Dec 30, 06

10 select convert(varchar, getdate(), 10) 12-30-06

11 select convert(varchar, getdate(), 11) 06/12/30

101 select convert(varchar, getdate(), 101) 12/30/2006

102 select convert(varchar, getdate(), 102) 2006.12.30

103 select convert(varchar, getdate(), 103) 30/12/2006

104 select convert(varchar, getdate(), 104) 30.12.2006

105 select convert(varchar, getdate(), 105) 30-12-2006

106 select convert(varchar, getdate(), 106) 30 Dec 2006

107 select convert(varchar, getdate(), 107) Dec 30, 2006

110 select convert(varchar, getdate(), 110) 12-30-2006

111 select convert(varchar, getdate(), 111) 2006/12/30

TIME FORMATS

8 or 108 select convert(varchar, getdate(), 8) 00:38:54

9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006

12:38:54:840AM

14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840



You can also format the date or time without dividing characters, as

well as concatenate the date and time string: Sample statement Output

select replace(convert(varchar, getdate(),101),'/','') 12302006

select replace(convert(varchar, getdate(),101),'/','') +

replace(convert(varchar, getdate(),108),':','')




Style ID

Style Type

0 or 100 mon dd yyyy hh:miAM (or PM)

101 mm/dd/yy

102 yy.mm.dd

103 dd/mm/yy

104 dd.mm.yy

105 dd-mm-yy

106 dd mon yy

107 Mon dd, yy

108 hh:mm:ss

9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)

110 mm-dd-yy

111 yy/mm/dd

112 yymmdd

13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)

114 hh:mi:ss:mmm(24h)

20 or 120 yyyy-mm-dd hh:mi:ss(24h)

21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)

126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

130 dd mon yyyy hh:mi:ss:mmmAM

131 dd/mm/yy hh:mi:ss:mmmAM


*http://msdn.microsoft.com/en-us/library/ms186819.aspx*

Comments

Post a Comment

Popular posts from this blog

URL Rewritting

http://www.simple-talk.com/dotnet/asp.net/a-complete-url-rewriting-solution-for-asp.net-2.0/ http://msdn.microsoft.com/en-us/library/ms972974.aspx URL Rewriting in ASP.NET Summary: Examines how to perform dynamic URL rewriting with Microsoft ASP.NET. URL rewriting is the process of intercepting an incoming Web request and automatically redirecting it to a different URL. Discusses the various techniques for implementing URL rewriting, and examines real-world scenarios of URL rewriting. (31 printed pages) Download the source code for this article. Contents Introduction Common Uses of URL Rewriting What Happens When a Request Reaches IIS Implementing URL Rewriting Building a URL Rewriting Engine Performing Simple URL Rewriting with the URL Rewriting Engine Creating Truly "Hackable" URLs Conclusion Related Books Introduction Take a moment to look at some of the URLs on your website. Do you find URLs like http://yoursite.com/info/dispEmployeeInfo.aspx?EmpID=459-099&type=summ...
Post a Comment
Read more

SEND A PDF FILE AS AN ATTACHEMENT OF MAIL!

System.Net.Mail.MailMessage m1 = new System.Net.Mail.MailMessage(); m1.From = "manpreet@gmail.com" m1.Subject = "Test mail "; m1.Body = str.ToString(); m1.IsBodyHtml = true; m1.To.Add("jasdeep@gmail.com"); m1.CC.Add("jasdeep123@gmail.com"); m1.Attachments.Add(new Attachment(strserverpath + @"\pdf\" + PdfFileName)); smtp.Send(m1);
Post a Comment
Read more

Sql Server Tips

IF WE WANT TO SELECT  TOP N ROWS  FROM A TABLE ,  WE USE THE FETCH NEXT  SELECT    * FROM  table1   ---//First m rows ignore first n rows ORDER BY id OFFSET n ROWS FETCH NEXT m ROWS ONLY if n=0, m=10 then first  10 rows if n=10, m=10 then first  10 rows start from 11th row Row_number()   SELECT  ROW_NUMBER() OVER(order by db.id) Insertion Simultaneouly in temp table with output clause DECLARE @table1 table (   Id int,   name nvarchar(50) ); DECLARE @table2 table (  Id int,  name nvarchar(50) ); INSERT INTO @table2 OUTPUT INSERTED.*   INTO @table1   select top 10 id, name  from FinalTable SELECT * FROM @table2; SELECT * FROM @table1;
Post a Comment
Read more