Problems with "While" of SQL Server

Question

Actually i need to use the following lines of TSQL queries to know the total date formats available in sql server. So i used the Variable called @count.

declare @count int=0;

While @count<500 begin

select convert(varchar,GETDATE(),@count)
set @count+=1;
End;

When i start execute this query. It is iterating from '0' to '14' and stop for 15. Because 15 is not the supported style in sql server. Though it is supported for 121, 130 and other numbers, I want to run the code upto 500 numbers.(Leaving the Non supported values) Can anyone tell me a suggestion to run this query an error Free One.

Thanks inadvance :-)

Consulting the documentation would seem a far saner idea. Even if you find a value that isn't documented, are you going to then build code that relies upon an undocumented value? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 17, 2014 at 13:13
Not to mention that there's some logic to the numbering - there's actually only about 30 different format numbers. — Luaan
– Luaan, Commented Jul 17, 2014 at 13:16
Damien_The_Unbeliever is correct, reading the documents is a much better approach, here's another link if you're curious. sqlusa.com/bestpractices/datetimeconversion — Chris L
– Chris L, Commented Jul 17, 2014 at 13:16
@Damien_The_Unbeliever I bet he tried documentation already :). But documentation does not cover all possible formats, and I think he's trying to list all possible, but not documented formats. (Altough it's risky to use them in development). — AdamL
– AdamL, Commented Jul 17, 2014 at 13:32

Community · Accepted Answer · 2017-05-23 11:51:23Z

3

Use TRY-CATCH to be able to continue after the error (available since Sql Server) 2008. It will fill the table with formatted dates when format is supported, or "ERROR" if it's not valid format number:

drop table #dates
GO
create table #dates (
    FormatNum int,
    FormattedDate varchar(255)
)
GO

declare @count int=0;
declare @strDate varchar(255)

While @count<500 begin
    set @strDate =''
    begin try 
        select @strDate=convert(varchar,GETDATE(),@count)
        insert into #dates(FormatNum, FormattedDate)
        select @count,@strDate
    end try
    begin catch
        insert into #dates(FormatNum, FormattedDate)
        select @count,'ERROR'
    end catch
    set @count+=1;
End
GO

select * from #dates

If you want to investigate into "hidden" date formats in SQL Sever, here's a question I asked some time ago about this topic, and received a good answer: undocumented CONVERT styles - datetime 23

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Jul 17, 2014 at 13:28

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serpiton · Accepted Answer · 2014-07-17 13:50:59Z

1

If you're using SQL Server 2012 or better you can change CONVERT with TRY_CONVERT, the latter returns NULL instead of an error if the conversion fails

DECLARE @count int = 0;

WHILE @count < 500
BEGIN
  SELECT TRY_CONVERT(varchar, GETDATE(), @count) Formatted
       , @count Style
  SET @count+=1;
END;

If you don't want the NULL use both

DECLARE @count int = 0;

WHILE @count < 500
BEGIN
  SELECT CONVERT(varchar, GETDATE(), @count) Formatted
       , @count Style
  WHERE  TRY_CONVERT(varchar, GETDATE(), @count) IS NOT NULL
  SET @count+=1;
END;

answered Jul 17, 2014 at 13:50

Serpiton

3,6843 gold badges26 silver badges35 bronze badges

Comments

Jesuraja · Accepted Answer · 2014-07-17 13:42:37Z

-1

Try this:

DECLARE @Count INT = 0;
DECLARE @List TABLE (Number INT, FormatDate VARCHAR(100))
DECLARE @FormatDate VARCHAR(100)
SET NOCOUNT ON
WHILE @Count<500
BEGIN
    BEGIN TRY
        SET @FormatDate = CONVERT(VARCHAR, GETDATE(), @Count)
        IF @@ERROR <> 0 GOTO NextCount

        INSERT INTO @List
        SELECT @Count, @FormatDate
    END TRY
    BEGIN CATCH
        -- Error
    END CATCH

    NextCount:
        SET @Count = @Count + 1
END
SET NOCOUNT OFF
SELECT * FROM @List

answered Jul 17, 2014 at 13:42

Jesuraja

3,8524 gold badges26 silver badges49 bronze badges

1 Comment

Jesuraja Over a year ago

Reason for down vote? Was the answer gave wrong output?

Collectives™ on Stack Overflow

Problems with "While" of SQL Server

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related