Date Formatting Issue

Question

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  

 

  

Answer 1

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.

Answer 2

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 |  
+-------------------------+------------------------+  

Answer 3

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.