Date Formats in SQL Server

1) SELECT CONVERT(VARCHAR,GETDATE(),100)

Result
May 13 2012 11:30AM

2) SELECT CONVERT(VARCHAR,GETDATE(),101)

Result
05/13/2012

3) SELECT CONVERT(VARCHAR,GETDATE(),102)

Result
2012.05.13

4) SELECT CONVERT(VARCHAR,GETDATE(),103)

Result
13/05/2012

5) SELECT CONVERT(VARCHAR,GETDATE(),104)

Result
13.05.2012

6) SELECT CONVERT(VARCHAR,GETDATE(),105)

Result
13-05-2012

7) SELECT CONVERT(VARCHAR,GETDATE(),106)

Result
13 May 2012

8) SELECT CONVERT(VARCHAR,GETDATE(),107)

Result
May 13, 2012

9) SELECT CONVERT(VARCHAR,GETDATE(),108)

Result
11:41:51

10) SELECT CONVERT(VARCHAR,GETDATE(),109)

Result
May 13 2012 11:43:04:267AM

11) SELECT CONVERT(VARCHAR,GETDATE(),110)

Result
05-13-2012

12) SELECT CONVERT(VARCHAR,GETDATE(),111)

Result
2012/05/13

13) SELECT CONVERT(VARCHAR,GETDATE(),112)

Result
20120513

14) SELECT CONVERT(VARCHAR,GETDATE(),113)

Result
13 May 2012 11:50:02:510

15) SELECT CONVERT(VARCHAR,GETDATE(),114)

Result
11:51:00:330

16) SELECT CONVERT(VARCHAR,GETutcDATE(),114)

Result
06:21:55:597

 

And if you are looking for a SQL query to find the SQL version, then try this

select @@version

 

download

 

Advertisements

Sort XML by date using xslt

For this, we just use the simple xml.

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="style.xslt"?>
 
<birthdays>
  <item>
    <date>13.05.2005</date>
    <title>John</title>
  </item>
  <item>
    <date>06.02.2000</date>
    <title>Steve</title>
  </item>
  <item>
    <date>13.01.2007</date>
    <title>Lisa</title>
  </item>
  <item>
    <date>29.05.2010</date>
    <title>Sarah</title>
  </item>
</birthdays>

And the xslt code would be something like this

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" encoding="utf-8"
doctype-public="-//W3C//DTD XHTML 1.0 Transitional//EN"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"/>
    
   <xsl:template match="birthdays">
    <h1>Birthdays</h1>
    <ul>
      <xsl:for-each select="item">
        <xsl:sort 
           select="normalize-space(substring(date,7,4))" 
           order="descending" />
        <xsl:sort 
           select="normalize-space(substring(date,4,2))" 
           order="descending" />
        <xsl:sort 
           select="normalize-space(substring(date,0,2))" 
           order="descending" />
        <li>
          <xsl:value-of select="date" />
          <br />
          <xsl:value-of select="title" />
        </li>
      </xsl:for-each>
    </ul>
    </xsl:template>
</xsl:stylesheet>

Output

Birthdays

  • 29.05.2010
    Sarah
  • 13.01.2007
    Lisa
  • 13.05.2005
    John
  • 06.02.2000
    Steve