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

Get all URLs in Umbraco using SQL

Use the below SQL script to get all the Urls in your Umbraco project.

; WITH PathXml AS (

/* -- This gives nodes with their 'urlName' property
 -- not in recycle bin,
 -- level > 1 which excludes the top level documents which are not included in the url */
 SELECT
 nodeId,
 cast([xml] as xml).query('data(//@urlName[1])').value('.', 'varchar(max)') AS Path
 FROM cmsContentXml x
 JOIN umbracoNode n ON x.nodeId = n.id AND n.trashed = 0 AND n.level > 1
)

SELECT
 u.id,
 u.path,
 '/' +
 /* This is to get the top level document */
 IsNull((SELECT
 pl.Path + '/'
 FROM PathXml pl
 WHERE ',' + u.path + ',' LIKE '%,' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',%'
 ORDER BY CHARINDEX(',' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',',
 ',' + u.path + ',')

FOR XML PATH('')),
 '') AS Url,
 u.text PageName
FROM umbracoNode u
WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' /* Document node */
AND trashed = 0
ORDER BY 3 /* Url */