none
Change datetime format

    Question

  • Hello

    i want to change datetime format now it is such this format "1/15/2010" and i want like this "15/1/2010" there code:

    USE main_db
    GO
    
    /***************** DATABASE STRUCTURE **********************/
    
    CREATE TABLE [dbo].[DimTime](
    	[TimeID] [int] IDENTITY(1,1) NOT NULL,
    	[FullDate] [datetime] NOT NULL,
    	[DayNumberOfMonth] [tinyint] NOT NULL,
    	[MonthName] [nvarchar](50) NOT NULL,
    	[MonthNumber] [nchar](10) NOT NULL,
    	[CalendarYear] [varchar](4) NOT NULL,
    	[DayNumberOfWeek] [tinyint] NOT NULL,
    	[DayNameOfWeek] [nvarchar](50) NOT NULL,
    	CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([TimeID])
    )
    
    
    GO
    
    
    /***************** DECLARATIONS *****************************/
    
    
    DECLARE @DATE_START DATETIME
    SET @DATE_START = '01/01/2009'
    
    DECLARE @DAYS_COUNT INT
    SET @DAYS_COUNT = 7000
    
    
    
    DECLARE @counter INT
    
    /***************** FILL TIME DIMENSION **********************/
    
    SET DATEFIRST 1
    
    PRINT 'Filling DimTime...'
    
    SET @counter = 1
    
    WHILE @counter <= @DAYS_COUNT
    BEGIN
    	DECLARE @curDate DATETIME
    	SET @curDate = DATEADD(day, @counter - 1, @DATE_START)
    	
    	INSERT INTO dbo.DimTime	
    		(FullDate, DayNumberOfMonth, MonthName, MonthNumber, 
    		CalendarYear, DayNumberOfWeek, DayNameOfWeek)
    	VALUES 
    		(@curDate, DATEPART(day, @curDate), DATENAME(month, @curDate), MONTH(@curDate),
    		YEAR(@curDate), DATEPART(weekday, @curDate), DATENAME(weekday, @curDate))
    		
    	SET @counter = @counter + 1
    END
    
    Monday, May 10, 2010 8:34 PM

Answers

  • Hello,

    If you want to change the presentation of datetime data, you can change the DATEFORMAT session option to change the format in a requested way.

    You can also use the CONVERT function to convert datetime to a required string.

    For DW/BI solution a common practice is to keep different versions of the datetime strings for different languages in the time dimension and use them later in reporting or cube translations.

    Miloslav Peterka

    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Monday, May 10, 2010 8:46 PM
  • you may use simply

     

    select convert(nvarchar(20),FullDate,103) from table

     

    this converts FullDate column to dd/mm/yyyy format

    detailed information :http://msdn.microsoft.com/en-us/library/ms187928.aspx


    http://www.sdtslmn.com
    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Monday, May 10, 2010 8:48 PM
  • Change FullDate Datatype from DATETIME to [VARCHAR] (20) and use CONVERT(VARCHAR(20),@curDate,103) in insert into statement loop. So that you can easily compare date in format(dd/mm/yyyy) with datetimepicker of your program.

    Look into modified code -

    CREATE TABLE #DimTime

    (

          [TimeID] [int] IDENTITY(1,1) NOT NULL,

          [FullDate] [VARCHAR] (20) NOT NULL,

          [DayNumberOfMonth] [tinyint] NOT NULL,

          [MonthName] [nvarchar](50) NOT NULL,

          [MonthNumber] [nchar](10) NOT NULL,

          [CalendarYear] [varchar](4) NOT NULL,

          [DayNumberOfWeek] [tinyint] NOT NULL,

          [DayNameOfWeek] [nvarchar](50) NOT NULL,

          CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([TimeID])

    )

    GO

     

     

     

    DECLARE @DATE_START DATETIME

    SET @DATE_START = '01/01/2009'

     

    DECLARE @DAYS_COUNT INT

    SET @DAYS_COUNT = 7000

     

    DECLARE @counter INT

     

    /***************** FILL TIME DIMENSION **********************/

     

    SET DATEFIRST 1

     

    PRINT 'Filling DimTime...'

     

    SET @counter = 1

     

    WHILE @counter <= @DAYS_COUNT

    BEGIN

          DECLARE @curDate DATETIME

          SET @curDate = DATEADD(day, @counter - 1, @DATE_START)

         

          INSERT INTO #DimTime   

                (FullDate, DayNumberOfMonth, MonthName, MonthNumber,

                CalendarYear, DayNumberOfWeek, DayNameOfWeek)

          VALUES

                (

               CONVERT(VARCHAR(20),@curDate,103),

               DATEPART(day, @curDate), 

               DATENAME(month, @curDate),

               MONTH(@curDate),

                   YEAR(@curDate),

               DATEPART(weekday, @curDate),

               DATENAME(weekday, @curDate)

            )

               

          SET @counter = @counter + 1

    END

    GO

     

    SELECT * FROM #DimTime 

    Thanks,

     

     

     


    Kapil Khalas
    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Tuesday, May 11, 2010 6:33 AM
  • I want this format dd/mm/yyyy  in the database,

    You need to store it as text like CHAR(10).

    Downside:

    1. It does not sort well

    2. You have to keep converting back to datetime for functions like DATEADD, DATEDIFF

    More on datatime conversion:

    http://www.sqlusa.com/bestpractices/datetimeconversion


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Tuesday, May 11, 2010 6:48 AM
  • Hello,

    Best practise for time dimension design is to use an integer value expressing the date as the primary key, not identity sequence, for example 20100428. It allows you to search fact tables by date without joining it to the time dimension. In addition, it is then easy to partition fact tables by date...

    Miloslav Peterka

    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Tuesday, May 11, 2010 7:25 AM

All replies

  • Hello,

    If you want to change the presentation of datetime data, you can change the DATEFORMAT session option to change the format in a requested way.

    You can also use the CONVERT function to convert datetime to a required string.

    For DW/BI solution a common practice is to keep different versions of the datetime strings for different languages in the time dimension and use them later in reporting or cube translations.

    Miloslav Peterka

    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Monday, May 10, 2010 8:46 PM
  • you may use simply

     

    select convert(nvarchar(20),FullDate,103) from table

     

    this converts FullDate column to dd/mm/yyyy format

    detailed information :http://msdn.microsoft.com/en-us/library/ms187928.aspx


    http://www.sdtslmn.com
    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Monday, May 10, 2010 8:48 PM
  • I want this format dd/mm/yyyy  in the database, i know how to get converted date from database but i have datetimepicker in my program, i compare datatimepicker's text to dates in database and  after that i can get requested date id and insert this id in the fact table's date_id.  Is this posible or exist better way in my case?
    Monday, May 10, 2010 9:28 PM
  • Change FullDate Datatype from DATETIME to [VARCHAR] (20) and use CONVERT(VARCHAR(20),@curDate,103) in insert into statement loop. So that you can easily compare date in format(dd/mm/yyyy) with datetimepicker of your program.

    Look into modified code -

    CREATE TABLE #DimTime

    (

          [TimeID] [int] IDENTITY(1,1) NOT NULL,

          [FullDate] [VARCHAR] (20) NOT NULL,

          [DayNumberOfMonth] [tinyint] NOT NULL,

          [MonthName] [nvarchar](50) NOT NULL,

          [MonthNumber] [nchar](10) NOT NULL,

          [CalendarYear] [varchar](4) NOT NULL,

          [DayNumberOfWeek] [tinyint] NOT NULL,

          [DayNameOfWeek] [nvarchar](50) NOT NULL,

          CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([TimeID])

    )

    GO

     

     

     

    DECLARE @DATE_START DATETIME

    SET @DATE_START = '01/01/2009'

     

    DECLARE @DAYS_COUNT INT

    SET @DAYS_COUNT = 7000

     

    DECLARE @counter INT

     

    /***************** FILL TIME DIMENSION **********************/

     

    SET DATEFIRST 1

     

    PRINT 'Filling DimTime...'

     

    SET @counter = 1

     

    WHILE @counter <= @DAYS_COUNT

    BEGIN

          DECLARE @curDate DATETIME

          SET @curDate = DATEADD(day, @counter - 1, @DATE_START)

         

          INSERT INTO #DimTime   

                (FullDate, DayNumberOfMonth, MonthName, MonthNumber,

                CalendarYear, DayNumberOfWeek, DayNameOfWeek)

          VALUES

                (

               CONVERT(VARCHAR(20),@curDate,103),

               DATEPART(day, @curDate), 

               DATENAME(month, @curDate),

               MONTH(@curDate),

                   YEAR(@curDate),

               DATEPART(weekday, @curDate),

               DATENAME(weekday, @curDate)

            )

               

          SET @counter = @counter + 1

    END

    GO

     

    SELECT * FROM #DimTime 

    Thanks,

     

     

     


    Kapil Khalas
    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Tuesday, May 11, 2010 6:33 AM
  • I want this format dd/mm/yyyy  in the database,

    You need to store it as text like CHAR(10).

    Downside:

    1. It does not sort well

    2. You have to keep converting back to datetime for functions like DATEADD, DATEDIFF

    More on datatime conversion:

    http://www.sqlusa.com/bestpractices/datetimeconversion


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Tuesday, May 11, 2010 6:48 AM
  • Hello,

    Best practise for time dimension design is to use an integer value expressing the date as the primary key, not identity sequence, for example 20100428. It allows you to search fact tables by date without joining it to the time dimension. In addition, it is then easy to partition fact tables by date...

    Miloslav Peterka

    • Marked as answer by Lasha89 Tuesday, May 11, 2010 12:53 PM
    Tuesday, May 11, 2010 7:25 AM