Category: SQL


SQL Server Recipe – Convert DateTime

Filed under: SQL — Leave a comment
December 21, 2009

Convert Datetime format to String and the various styles

The following SQL code will convert a Datetime value to String. This particular query is converting the current date as an example. The number ’112′ is a style code that SQL server uses. This will return the date in YYYYMMDD format. There is also a table of the various Style Codes and the date format that they will return below that. Just insert the particular style code you want into the sql query to get the desired result.


select CONVERT(CHAR(8), getdate(), 112) as 'Date Format'

Date Format Style
Sep 16 2 0
09/16/09 1
09.09.16 2
16/09/09 3
16.09.09 4
16-09-09 5
16 Sep 0 6
Sep 16, 7
18:20:31 8
Sep 16 2 9
09-16-09 10
09/09/16 11
090916 12
16 Sep 2 13
18:20:31 14
2009-09- 20
2009-09- 21
09/16/09 22
2009-09- 23
18:20:31 24
2009-09- 25
Sep 16 2 100
09/16/20 101
2009.09. 102
16/09/20 103
16.09.20 104
16-09-20 105
16 Sep 2 106
Sep 16, 107
18:20:31 108
Sep 16 2 109
09-16-20 110
2009/09/ 111
20090916 112
16 Sep 2 113
18:20:31 114
2009-09- 120
2009-09- 121
2009-09- 126
2009-09- 127
27 ????? 130
27/09/14 131
Comment

SQL Server Recipe – Find Field Value in Database

Filed under: SQL — Leave a comment
December 20, 2009

So, I am still struggling to find time to update this blog. And, thus, the identity of this blog is still pretty nebulous. I really would like to find some time sometime soon.

At any rate, I do work in IT, and I work with MS SQL Server a lot. So I have some “recipes” that I thought I would post here. This way I would be sure they “go with me” wherever I go. :)

Hopefully they can help someone else out, too. Let me know in the comments if anyone finds any of these useful or not.

Here is the first one:

Find Field Value in a Database

The following is a SQL Script that can be run in a database to return all tables and columns where a particular value is present. This can be used for strings or values with a small modification. Just place the value you’re looking for where it says ‘valuehere.’ No need to put wildcards, the SQL does that for you. This script is for MS SQL Server 2005.


DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)

SET @value = 'valuehere'

CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)

DECLARE TABLES CURSOR
FOR

SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name

OPEN TABLES

FETCH NEXT FROM TABLES
INTO @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
--SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'

EXEC(@sql)

FETCH NEXT FROM TABLES
INTO @table, @column
END

CLOSE TABLES
DEALLOCATE TABLES

SELECT *
FROM #t

DROP TABLE #t

Comment
Follow

Get every new post delivered to your Inbox.