Date Formatting Issue

Malam Malam 121 Reputation points
2021-01-15T16:30:44.767+00:00

I have the folowing in my SQL query, it formats all dates correctly but messes up for the month of February.
For example: Feb 1 2020 12:00AM.
There is an extra space between "Feb" and "1" instead of having just one space like Feb 1 2020 12:00AM.

How do I fix it?

declare @startDate datetime, @endDate dateTime, @month int, @year varchar(4)  
set @month= 1  
  
while @month < 13  
begin  
	set @startDate =CONVERT(DATETIME, Str(@month) + '/01/' + Str(Year(GetDate())-1))  
	set @endDate =  Convert(DateTime, Str(@month) +'/' +STR(DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(@startDate),   
					@startDate)) - 1)) +'/' + STR(Year(GetDate())))  
  
	while @startDate <= @endDate  
	begin  

 

  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-01-18T02:44:05.167+00:00

    Hi @Malam Malam ,

    Welcome to Microsoft Q&A!

    I got below error with your query even though I added 'SET @month=@month+1' due to there were only 28 or 29 days in February.

    Msg 242, Level 16, State 3, Line 8
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    I updated part of your query and you could check whether it is a little helpful to you.

    declare @startDate datetime, @endDate dateTime, @month int, @year varchar(4)  
    set @month= 1  
    set @year=2020  
          
    while @month <13  
    begin  
        set @startDate =CONVERT(DATETIME, Str(@month) + '/01/' + Str(Year(GetDate())-1),110)  
      
     if @month<>2    
     set @endDate =  Convert(DateTime, Str(@month) +'/' +STR(DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(@startDate),   
                        @startDate)) - 1)) +'/' + STR(Year(GetDate())),110)  
     else   --calculate the last day of Februday whatever it is a leap year  
     set @endDate =  DATEADD(DD,-1,CONVERT(DATETIME,CAST(@year * 10000 + (@month + 1) * 100 + 1 as CHAR(10)),110))   
      
        SET @month=@month+1  
      
     select FORMAT( @startDate, 'MMM d yyyy hh:mm:ss tt', 'en-US' ) startdate  
     select FORMAT( @endDate, 'MMM d yyyy hh:mm:ss tt', 'en-US' )  endDate  
    end    
    

    One of the output :
    Feb 1 2020 12:00:00 AM

    Besides, as mentioned by other experts, you could also export the data to the Excel and format them there instead.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


2 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,206 Reputation points
    2021-01-15T16:46:59.723+00:00

    T-SQL FORMAT() function gives you a full formatting control.
    It implements .Net framework formatting via .Net CLR.
    Useful link: custom-date-and-time-format-strings

    In the time format portion:

    • if you use "hh" ->> The hour, using a 12-hour clock from 01 to 12.
    • if you use "HH" ->> The hour, using a 24-hour clock from 00 to 23.
    • if you add "tt" ->> The AM/PM designator.

    SQL

    DECLARE @var DATE = '2020-02-01';  
    SELECT @var AS [Before], FORMAT(@var, 'MMM d yyyy') AS [After];  
    

    Output

    +------------+------------+  
    |   Before   |   After    |  
    +------------+------------+  
    | 2020-02-01 | Feb 1 2020 |  
    +------------+------------+  
    

    SQL

    DECLARE @var DATETIME = '2020-02-01T12:00:00';  
    SELECT @var AS [Before], FORMAT(@var, 'MMM d yyyy hh:mm:ss tt') AS [After];  
    

    Output

    +-------------------------+------------------------+  
    |         Before          |         After          |  
    +-------------------------+------------------------+  
    | 2020-02-01 12:00:00.000 | Feb 1 2020 12:00:00 PM |  
    +-------------------------+------------------------+  
    
    0 comments No comments

  2. Malam Malam 121 Reputation points
    2021-01-15T17:43:23.69+00:00

    Do you see any issues with my code? There are 2 spaces between "Feb" and "1"
    Feb^^1^2020^12:00AM

    If I copy this value in Excel and format to a date, Excel won't format this value.