Categories
Computing

SQL Server Date to Varchar Conversion Formats

Often, I find the need for a specific format for a date. SQL has lots of great options with its convert function.

Here’s a handy chart.

select convert(varchar, getdate(), 101) -- 12/23/2015
select convert(varchar, getdate(), 102) -- 2015.12.23
select convert(varchar, getdate(), 103) -- 23/12/2015
select convert(varchar, getdate(), 104) -- 23.12.2015
select convert(varchar, getdate(), 105) -- 23-12-2015
select convert(varchar, getdate(), 106) -- 23 Dec 2015
select convert(varchar, getdate(), 107) -- Dec 23, 2015
select convert(varchar, getdate(), 108) -- 08:32:49
select convert(varchar, getdate(), 109) -- Dec 23 2015 8:33:06:007AM
select convert(varchar, getdate(), 110) -- 12-23-2015
select convert(varchar, getdate(), 111) -- 2015/12/23
select convert(varchar, getdate(), 112) -- 20151223
select convert(varchar, getdate(), 113) -- 23 Dec 2015 08:33:25:220
select convert(varchar, getdate(), 114) -- 08:33:25:220
select convert(varchar, getdate(), 115) -- 115 is not a valid style number when converting from datetime to a character string.
select convert(varchar, getdate(), 116) -- 116 is not a valid style number when converting from datetime to a character string.
select convert(varchar, getdate(), 117) -- 117 is not a valid style number when converting from datetime to a character string.
select convert(varchar, getdate(), 118) -- 118 is not a valid style number when converting from datetime to a character string.
select convert(varchar, getdate(), 119) -- 119 is not a valid style number when converting from datetime to a character string.
select convert(varchar, getdate(), 120) -- 2015-12-23 08:34:39
select convert(varchar, getdate(), 121) -- 2015-12-23 08:35:46.370

Feature Photo by Andrew Neel on Unsplash

Categories
Computing

Using a Correlated Subquery to Update a Base Table

The syntax for updating using correlated subqueries can be a little confusing at times. The example shown below uses a table variable as its target.

The key is to include an aliased version of the table in the UPDATE’s FROM clause.

Once that is aliased, you can simply reference the alias in the subquery.

For example:
UPDATE
    x
SET
    PROJCOMMENTID = 
    (
        SELECT 
            MAX(pc.PROJCOMMENTID) 
        FROM 
            ProjectComments pc (NOLOCK) 
        WHERE 
            pc.PROJECTID = x.PROJECTID
    )
FROM
    @ProjComments x

Featured Photo by Maarten Deckers on Unsplash