How to remove formatting from the GETDATE () command

Question

How to remove formatting from the GETDATE () command

Navigation

#1 by (6 votes)
#2 by (4 votes)

5

I'm developing an application that will be used by multiple clients simultaneously, I'd like to use Day, Month, Year, Hour, Minute, Second and Millisecond as id and / that the : function returns?

sql sql-server

asked by anonymous 28.05.2015 / 14:32

2 answers

How to deal with multiple queries? How do I return the ID of a record right after it is entered?

score 6 · Answer 1

6

For SQL Server from 2012, use FORMAT()

SELECT FORMAT( GETDATE(), 'yyyyMMddHHmmssfff' );

See the Date Format Character Patterns .

28.05.2015 / 14:54

score 4 · Answer 2

If it is SQL-SERVER 2008 or previous, the function FORMAT , very well applied by friend @gmsantos will not work ; You can do this to remove special characters:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

Then just execute the query with the function, remembering to convert the result of GETDATE() :

SELECT dbo.fn_StripCharacters(CONVERT(VARCHAR, GETDATE(), 120), '^a-z0-9')

The result will be: 20150528095612

Source: SOen