My Personal Blog's
SQL Server
How to format date time in SQL? – SQL Server Date Time Format
Jan 15th
getdate() = Jan 14 2010 10:00:05:190PM
| Format | Query | Result |
| USA mm/dd/yy | select convert(varchar, getdate(), 1) | 01/14/10 |
| ANSI yy.mm.dd | select convert(varchar, getdate(), 2) | 10.01.14 |
| British/French dd/mm/yy | select convert(varchar, getdate(), 3) | 14/01/10 |
| German dd.mm.yy | select convert(varchar, getdate(), 4) | 14.01.10 |
| Italian dd-mm-yy | select convert(varchar, getdate(), 5) | 14-01-10 |
| dd mon yy | select convert(varchar, getdate(), 6) | 14 Jan 10 |
| Mon dd, yy | select convert(varchar, getdate(), 7) | Jan 14, 10 |
| HH:MI:SS | select convert(varchar, getdate(), 8 ) | 21:54:31 |
| Mon dd yyyy H:MI:SS:msAM/PM | select convert(varchar, getdate(), 9) | Jan 14 2010 9:54:56:490PM |
| USA mm-dd-yy | select convert(varchar, getdate(), 10) | 01-14-10 |
| JAPAN yy/mm/dd | select convert(varchar, getdate(), 11) | 10/01/14 |
| ISO yymmdd | select convert(varchar, getdate(), 12) | 100114 |
| dd Mon yyyy HH:MI:MS:SS | select convert(varchar, getdate(), 13) | 14 Jan 2010 21:57:39:070 |
| HH:MI:MS:SS | select convert(varchar, getdate(), 14) | 21:58:21:263 |
| mon dd yyyy hh:miAM (or PM) | select convert(varchar, getdate(), 100) | Jan 14 2010 9:59PM |
| mm/dd/yyyy | select convert(varchar, getdate(), 101) | 01/14/2010 |
| yyyy.mm.dd | select convert(varchar, getdate(), 102) | 2010.01.14 |
| dd/mm/yyyy | select convert(varchar, getdate(), 103) | 14/01/2010 |
| dd.mm.yyyy | select convert(varchar, getdate(), 104) | 14.01.2010 |
| dd-mm-yyyy | select convert(varchar, getdate(), 105) | 14-01-2010 |
| dd mon yyyy | select convert(varchar, getdate(), 106) | 14 Jan 2010 |
| Mon dd, yyyy | select convert(varchar, getdate(), 107) | Jan 14, 2010 |
| hh:mm:ss | select convert(varchar, getdate(), 108) | 21:59:59 |
| Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) | select convert(varchar, getdate(), 109) | Jan 14 2010 10:00:05:190PM |
| mm-dd-yyyy | select convert(varchar, getdate(), 110) | 01-14-2010 |
| yyyy/mm/dd | select convert(varchar, getdate(), 111) | 2010/01/14 |
| yyyymmdd | select convert(varchar, getdate(), 112) | 20100114 |
| Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) | select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13) | 14 Jan 2010 22:00:35:107 |
| hh:mi:ss:mmm(24h) | select convert(varchar, getdate(), 114) | 22:00:40:423 |
References:
http://www.technoreader.com/SQL-Server-Date-Time-Format.aspx
http://www.sql-server-helper.com/tips/date-formats.aspx
SSIS: Update data from different table if data is Null
Nov 30th
SSIS: SSIS stands for SQL Server Integration Services. It is the new data transformation standard for SQL Server 2005 and has replaced the old SQL Server Data Transformation Services.
In this post, I want to show how to update data from different table with condition that the data is Null.
Read the rest of this entry »
Microsoft SQL Server 2005 Cursor Example
May 19th
DECLARE @id INT
DECLARE @name varchar(25)
DECLARE db_cursor CURSOR FOR
SELECT id,name From myTable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
– do something
FETCH NEXT FROM db_cursor INTO @id, @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
