Not sure if I should post it here, maybe it’s just me lacking some tricks, but I noticed that GPT (either 3.5-16k or 4) cannot make a exhaustive lists of items I asked, from a short text (as short as about 850 words).
Here is the texts I used:
Date and Time Conversions Using SQL Server
Problem
There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it.
One option is to format the data in the application itself.
Another option is to use the built-in functions SQL Server provides to format the date string for you.Solution
SQL Server provides a number of options you can use for formatting a date/time string in SQL queries and stored procedures either from an input file (Excel, CSV, etc.) or a date column (datetime, datetime2, smalldatetime, etc.) from a table.One of the first considerations is the actual date/time value needed. The most common is the current date/time using getdate() (/sqlservertip/6817/sql-current-date/).
This provides the current date and time according to the server providing the date and time.
If a universal date/time (UTC) is needed, then getutcdate() (/sqlservertip/6817/sql-current-date/) should be used.
To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed.
How to get different date formats in SQL Server
- Use the SELECT statement with CONVERT function and date format option
for the date values needed- To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
- To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
- Check out the chart to get a list of all format options
Below is a list of SQL date formats and an example of the output. The date used for all of these examples is “2006-12-30 00:38:54.840”.DATE ONLY FORMATS
Format # Query Format Sample
1 select convert(varchar, getdate(), 1) mm/dd/yy 12/30/06
2 select convert(varchar, getdate(), 2) yy.mm.dd 06.12.30
3 select convert(varchar, getdate(), 3) dd/mm/yy 30/12/06
4 select convert(varchar, getdate(), 4) dd.mm.yy 30.12.06
5 select convert(varchar, getdate(), 5) dd-mm-yy 30-12-06
6 select convert(varchar, getdate(), 6) dd-Mon-yy 30 Dec 06
7 select convert(varchar, getdate(), 7) Mon dd, yy Dec 30, 06
10 select convert(varchar, getdate(), 10) mm-dd-yy 12-30-06
11 select convert(varchar, getdate(), 11) yy/mm/dd 06/12/30
12 select convert(varchar, getdate(), 12) yymmdd 061230
23 select convert(varchar, getdate(), 23) yyyy-mm-dd 2006-12-30
101 select convert(varchar, getdate(), 101) mm/dd/yyyy 12/30/2006
102 select convert(varchar, getdate(), 102) yyyy.mm.dd 2006.12.30
103 select convert(varchar, getdate(), 103) dd/mm/yyyy 30/12/2006
104 select convert(varchar, getdate(), 104) dd.mm.yyyy 30.12.2006
105 select convert(varchar, getdate(), 105) dd-mm-yyyy 30-12-2006
106 select convert(varchar, getdate(), 106) dd Mon yyyy 30 Dec 2006
107 select convert(varchar, getdate(), 107) Mon dd, yyyy Dec 30, 2006
110 select convert(varchar, getdate(), 110) mm-dd-yyyy 12-30-2006
111 select convert(varchar, getdate(), 111) yyyy/mm/dd 2006/12/30
112 select convert(varchar, getdate(), 112) yyyymmdd 20061230TIME ONLY FORMATS
8 select convert(varchar, getdate(), 8) hh:mm:ss 00:38:54
14 select convert(varchar, getdate(), 14) hh:mm:ss:nnn 00:38:54:840
24 select convert(varchar, getdate(), 24) hh:mm:ss 00:38:54
108 select convert(varchar, getdate(), 108) hh:mm:ss 00:38:54
114 select convert(varchar, getdate(), 114) hh:mm:ss:nnn 00:38:54:840DATE & TIME FORMATS
0 select convert(varchar, getdate(), 0) Mon dd yyyy hh:mm AM/PM Dec 30 2006 12:38AM
9 select convert(varchar, getdate(), 9) Mon dd yyyy hh:mm:ss:nnn AM/PM Dec 30 2006 12:38:54:840AM
13 select convert(varchar, getdate(), 13) dd Mon yyyy hh:mm:ss:nnn AM/PM 30 Dec 2006 00:38:54:840AM
20 select convert(varchar, getdate(), 20) yyyy-mm-dd hh:mm:ss 2006-12-30 00:38:54
21 select convert(varchar, getdate(), 21) yyyy-mm-dd hh:mm:ss:nnn 2006-12-30 00:38:54.840
22 select convert(varchar, getdate(), 22) mm/dd/yy hh:mm:ss AM/PM 12/30/06 12:38:54 AM
25 select convert(varchar, getdate(), 25) yyyy-mm-dd hh:mm:ss:nnn 2006-12-30 00:38:54.840
100 select convert(varchar, getdate(), 100) Mon dd yyyy hh:mm AM/PM Dec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Mon dd yyyy hh:mm:ss:nnn AM/PM Dec 30 2006 12:38:54:840AM
113 select convert(varchar, getdate(), 113) dd Mon yyyy hh:mm:ss:nnn 30 Dec 2006 00:38:54:840
120 select convert(varchar, getdate(), 120) yyyy-mm-dd hh:mm:ss 2006-12-30 00:38:54
121 select convert(varchar, getdate(), 121) yyyy-mm-dd hh:mm:ss:nnn 2006-12-30 00:38:54.840
126 select convert(varchar, getdate(), 126) yyyy-mm-dd T hh:mm:ss:nnn 2006-12-30T00:38:54.840
127 select convert(varchar, getdate(), 127) yyyy-mm-dd T hh:mm:ss:nnn 2006-12-30T00:38:54.840
ISLAMIC CALENDAR DATES
130 select convert(nvarchar, getdate(), 130) dd mmm yyyy hh:mi:ss:nnn AM/PM
131 select convert(nvarchar, getdate(), 131) dd mmm yyyy hh:mi:ss:nnn AM/PM 10/12/1427 12:38:54:840AMYou can also format the date or time without dividing characters, as well as concatenate the date and time string:
Sample statement Format Output
select replace(convert(varchar, getdate(),101),‘/’,‘’) mmddyyyy 12302006
select replace(convert(varchar, getdate(),101),‘/’,‘’) + replace(convert(varchar,getdate(),108),‘:’,‘’) mmddyyyyhhmmss 12302006004426If you want to get a list of all valid date and time formats, you could use the code below,
and change the @date to GETDATE() or any other date you want to use.
This will output just the valid formats.DECLARE @counter INT = 0
DECLARE @date DATETIME = ‘2006-12-30 00:38:54.840’
CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))
WHILE (@counter <= 150 )
BEGIN
BEGIN TRY
INSERT INTO #dateFormats
SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter)
SET @counter = @counter + 1
END TRY
BEGIN CATCH;
SET @counter = @counter + 1
IF @counter >= 150
BEGIN
BREAK
END
END CATCH
END
SELECT * FROM #dateFormats
Here is my request for a list:
for both Date Only Formats and Date Time Formats: find each and every one of the format, which put month (‘Mon’ or ‘mm’) at the beginning.
The best result I get so far is:
Date Only Formats:
6. select convert(varchar, getdate(), 6) dd-Mon-yy
7. select convert(varchar, getdate(), 7) Mon dd, yy
106. select convert(varchar, getdate(), 106) dd Mon yyyy
107. select convert(varchar, getdate(), 107) Mon dd, yyyy
113. select convert(varchar, getdate(), 113) dd Mon yyyyDate & Time Formats:
13. select convert(varchar, getdate(), 13) dd Mon yyyy hh:mm:ss:nnn AM/PM
22. select convert(varchar, getdate(), 22) mm/dd/yy hh:mm:ss AM/PM
No matter how I change my model, prompts, files… it simply do not give correct results. If GPT cannot even finish this simple(?) task on a short texts(about 850 words only!), how can I trust the results from RAG, in general?
Or it could just be me lacking some tricks?