//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: What are the valid styles for converting datetime to string?


What are the valid styles for converting datetime to string?

I wrote this little table and procedure to help me remember what style 112 did, or how to get HH:MM AM/PM out of a DATETIME column. Basically, it populates a table with the valid style numbers, then loops through those, and produces the result (and the syntax for producing that result) for each style, given the current date and time. 
 
Yes, it uses a cursor. This is designed to be a helper function, not something you would use as part of a production environment, so I don't think the performance implications should be a big concern. But if you have  
 
USE tempdb; 
GO 
 
CREATE PROCEDURE dbo.help_DateTimeFormats 
    @styleID TINYINT = NULL 
AS 
BEGIN 
    SET NOCOUNT ON; 
     
    IF OBJECTPROPERTY 
    ( 
        OBJECT_ID('dbo.DateTimeFormats'), 
        'IsUserTable' 
    ) = 1 
    BEGIN 
        DROP TABLE dbo.DateTimeFormats; 
    END 
 
    CREATE TABLE dbo.DateTimeFormats  
    (  
        styleID TINYINT PRIMARY KEY,  
        outputLength TINYINT, 
        outputSyntax VARCHAR(64), 
        outputSample VARCHAR(255) 
    ); 
     
    INSERT dbo.DateTimeFormats(styleID, outputLength) 
        SELECT style = 0, outputLength = 19 
        UNION SELECT 1, 8  
        UNION SELECT 2, 8  
        UNION SELECT 3, 8  
        UNION SELECT 4, 8  
        UNION SELECT 5, 8  
        UNION SELECT 6, 9  
        UNION SELECT 7, 10  
        UNION SELECT 8, 8  
        UNION SELECT 9, 26  
        UNION SELECT 10, 8  
        UNION SELECT 11, 8  
        UNION SELECT 12, 6  
        UNION SELECT 13, 24  
        UNION SELECT 14, 12  
        UNION SELECT 20, 19  
        UNION SELECT 21, 23  
        UNION SELECT 22, 20  
        UNION SELECT 23, 10  
        UNION SELECT 24, 8  
        UNION SELECT 25, 23  
        UNION SELECT 100, 19  
        UNION SELECT 101, 10  
        UNION SELECT 102, 10  
        UNION SELECT 103, 10  
        UNION SELECT 104, 10  
        UNION SELECT 105, 10  
        UNION SELECT 106, 11  
        UNION SELECT 107, 12  
        UNION SELECT 108, 8  
        UNION SELECT 109, 26  
        UNION SELECT 110, 10  
        UNION SELECT 111, 10  
        UNION SELECT 112, 8  
        UNION SELECT 113, 24  
        UNION SELECT 114, 12  
        UNION SELECT 120, 19  
        UNION SELECT 121, 23 
        UNION SELECT 126, 23 
        UNION SELECT 130, 32 
        UNION SELECT 131, 25; 
 
    IF CHARINDEX('SQL Server 2005', @@VERSION) > 0  
        INSERT dbo.DateTimeFormats(styleID, outputLength) 
            SELECT 127, 23; -- 127 is new in 2005 
 
    UPDATE dbo.DateTimeFormats  
        SET outputSyntax = 'CONVERT(CHAR(' 
            + RTRIM(outputLength) + '), CURRENT_TIMESTAMP, ' 
            + RTRIM(styleID) + ')'; 
 
    DECLARE 
        @sql VARCHAR(1024), 
        @style TINYINT, 
        @syntax VARCHAR(64); 
 
    DECLARE c CURSOR 
        LOCAL FORWARD_ONLY STATIC READ_ONLY FOR  
        SELECT styleID, outputSyntax  
            FROM dbo.DateTimeFormats; 
             
    OPEN c; FETCH NEXT FROM c INTO @style, @syntax; 
 
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 
        SET @sql = 'UPDATE dbo.DateTimeFormats 
            SET outputSample = ' + @syntax + ' 
            WHERE styleID = ' + RTRIM(@style) + ';'; 
             
        EXEC(@sql); 
         
        FETCH NEXT FROM c INTO @style, @syntax; 
    END 
 
    CLOSE c; DEALLOCATE c; 
 
    SELECT 
        styleID, 
        outputSample, 
        outputSyntax 
    FROM 
        dbo.DateTimeFormats 
    WHERE 
        styleID = COALESCE(@styleID, styleID);  
 
    DROP TABLE dbo.DateTimeFormats;  
END 
GO 
 
EXEC dbo.help_DateTimeFormats; 
EXEC dbo.help_DateTimeFormats @styleID = 112; 
 
-- DROP PROCEDURE dbo.help_DateTimeFormats;
 
The output should look like this: 
 
0Feb 22 2006 4:26PMCONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)
102/22/06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)
206.02.22CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)
322/02/06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)
422.02.06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)
522-02-06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)
622 Feb 06CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)
7Feb 22, 06CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)
816:26:08CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)
9Feb 22 2006 4:26:08:020PMCONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)
1002-22-06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)
1106/02/22CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)
12060222CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)
1322 Feb 2006 16:26:08:020CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13)
1416:26:08:037CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14)
202006-02-22 16:26:08CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20)
212006-02-22 16:26:08.037CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)
2202/22/06 4:26:08 PMCONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)
232006-02-22CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)
2416:26:08CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)
252006-02-22 16:26:08.037CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)
100Feb 22 2006 4:26PMCONVERT(CHAR(19), CURRENT_TIMESTAMP, 100)
10102/22/2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101)
1022006.02.22CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)
10322/02/2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
10422.02.2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104)
10522-02-2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105)
10622 Feb 2006CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106)
107Feb 22, 2006CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)
10816:26:08CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108)
109Feb 22 2006 4:26:08:067PMCONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)
11002-22-2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110)
1112006/02/22CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111)
11220060222CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)
11322 Feb 2006 16:26:08:067CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113)
11416:26:08:067CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)
1202006-02-22 16:26:08CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120)
1212006-02-22 16:26:08.080CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)
1262006-02-22T16:26:08.080CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
1272006-02-22T16:26:08.080CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127)
13024 ???? 1427 4:26:08:080PM CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)
13124/01/1427 4:26:08:080PMCONVERT(CHAR(25), CURRENT_TIMESTAMP, 131)

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 6/26/2003 | Last Updated: 2/22/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (189)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...