SQL returned data

[bottomleypotts]

I use a Microsoft SQL Server to store data. Sometimes when I interrogate it, dates return in a format of 'yyyy:mm:dd' and other times it will return a date as 'dd/mm/yyyy'. Can anyone suggest a method where dates return one way or the other all the time?

I'm using 'ADODB.Connection' and a connection string to open the database.

Any help would be appreciated! I hate having to test query results all the time for different date formats, and I hate having to issue SQL with dates formatted with every query. It becomes tedious!

[stanl]

SELECT format([datefield],'dd/MM/yyyy')  is the best guess at this point.


other examples: the last one uses the optional 'culture' parameter



Query                                               Sample output
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date           21/03/2018
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date   21/03/2018, 11:36:14
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date       Wednesday, March, 2018
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date           Mar 21 2018
SELECT FORMAT (getdate(), 'MM.dd.yy') as date               03.21.18
SELECT FORMAT (getdate(), 'MM-dd-yy') as date               03-21-18
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date           11:36:14 AM
SELECT FORMAT (getdate(), 'd','us') as date                   03/21/2018

[jmburton2001]

I developed a utility to extract dates from log files for users across the world with any unknown combination of YMD placement and unknown separators. I used the following registry keys to read the date format for the user's system and convert them "on the fly" to the YYYY.MM.DD.hh.mm.ss format. It added virtually no overhead to the processing and then I could convert and output to any consistent date format I wanted.

HKEY_CURRENT_USER\Control Panel\International\sLongDate
HKEY_CURRENT_USER\Control Panel\International\sShortDate

Once I was able to determine the position of the date elements and the separators, I wrote a fairly detailed "calculator" to make the conversion. After that it didn't matter where the machine was or what date format they were using.

Disclaimer -> This may, or may not, be useful. As usual, this opinion is worth two cents (or less)...  ;)

[stanl]

I developed a utility to extract dates from log files for users across the world

That sounds pretty cool. Love to see an example. However, seems the opp already has data in a db table as type Data/Datetime.... so ADO/SQL conversions functions I would hope will suffice.


The only other thing I can think of is the Op has data stored for Epoch conversions, i.e.
1625682330  which would translate to Wednesday, July 7, 2021 6:25:30 PM [GMT]

[jmburton2001]

That sounds pretty cool. Love to see an example.

The script is for specific log files generated by EPG123. The entire utility is extensive and won't run on a machine that doesn't have a working copy of the program, but this is the "meat" of what I was referring to and most likely contains elements used for other areas.

Code (winbatch) Select Expand

; Start collecting system info
:SERegQuery
SELocale = RegQueryValue(@REGCURRENT,"Control Panel\International[Locale]")
SELocaleName = RegQueryValue(@REGCURRENT,"Control Panel\International[LocaleName]")
SECountry = RegQueryValue(@REGCURRENT,"Control Panel\International[sCountry]")
SEDateSep = RegQueryValue(@REGCURRENT,"Control Panel\International[sDate]")
SEShortDt = StrUpper (RegQueryValue(@REGCURRENT,"Control Panel\International[sShortDate]"))
SERawShortDt = RegQueryValue(@REGCURRENT,"Control Panel\International[sShortDate]")
SEMonthList = StrCat ("January",@TAB,"February",@TAB,"March",@TAB,"April",@TAB,"May",@TAB,"June",@TAB,"July",@TAB,"August",@TAB,"September",@TAB,"October",@TAB,"November",@TAB,"December")

; Find date elements and separators
If SEDateSep == "/" Then SEDateSepW = "Forward slash"
If SEDateSep == "." Then SEDateSepW = "Period"
If SEDateSep == " " Then SEDateSepW = "Blank space"
If SEDateSep == "-" Then SEDateSepW = "Dash"
SEShortDt1 = StrFixChars (ItemExtract (1, SEShortDt, SEDateSep), " ",1)
SEShortDt2 = StrFixChars (ItemExtract (2, SEShortDt, SEDateSep), " ",1)
SEShortDt3 = StrFixChars (ItemExtract (-1, SEShortDt, SEDateSep), " ",1)
If SEShortDt1 == "M" Then SEShortDt1 = StrFixChars (SEShortDt1, "M",2)
If SEShortDt1 == "D" Then SEShortDt1 = StrFixChars (SEShortDt1, "D",2)
If SEShortDt1 == "Y" Then SEShortDt1 = StrFixChars (SEShortDt1, "Y",4)
If SEShortDt2 == "M" Then SEShortDt2 = StrFixChars (SEShortDt2, "M",2)
If SEShortDt2 == "D" Then SEShortDt2 = StrFixChars (SEShortDt2, "D",2)
If SEShortDt2 == "Y" Then SEShortDt2 = StrFixChars (SEShortDt2, "Y",4)
If SEShortDt3 == "M" Then SEShortDt3 = StrFixChars (SEShortDt3, "M",2)
If SEShortDt3 == "D" Then SEShortDt3 = StrFixChars (SEShortDt3, "D",2)
If SEShortDt3 == "Y" Then SEShortDt3 = StrFixChars (SEShortDt3, "Y",4)
SEShortDt = StrCat (SEShortDt1, SEDateSep, SEShortDt2, SEDateSep, SEShortDt3)

If SEFrom1 <= "12" && SEShortDt1 == "MM" Then SESMonth = ItemExtract (SEFrom1, SEMonthList, @TAB)
If SEFrom1 <= "31" && SEShortDt1 == "DD" Then SESDay = SEFrom1
If SEFrom1 >= "2000" && SEShortDt1 == "YYYY" Then SESYear = SEFrom1

If SEFrom2 <= "12" && SEShortDt2 == "MM" Then SESMonth = ItemExtract (SEFrom2, SEMonthList, @TAB)
If SEFrom2 <= "31" && SEShortDt2 == "DD" Then SESDay = SEFrom2
If SEFrom2 >= "2000" && SEShortDt2 == "YYYY" Then SESYear = SEFrom2

If SEFrom3 <= "12" && SEShortDt3 == "MM" Then SESMonth = ItemExtract (SEFrom3, SEMonthList, @TAB)
If SEFrom3 <= "31" && SEShortDt3 == "DD" Then SESDay = SEFrom3
If SEFrom3 >= "2000" && SEShortDt3 == "YYYY" Then SESYear = SEFrom3

If SESMonth == "" Then Message ("ERROR!", "Date Error Detected!%@CRLF%%@CRLF%Now generating diagnostic report...%@CRLF%%@CRLF%Thank you!")
If SESMonth == "" Then DateError = "Blank Month Detected at Line 120"
If SESMonth == "" Then SEDateGuess = ""
If SESMonth == "" Then ToDate = ""
If SESMonth == "" Then CalFrom = ""
If SESMonth == "" Then CalTo = ""
If SESMonth == "" Then Goto SESysReport
If SESDay == "" Then Message ("ERROR!", "Date Error Detected!%@CRLF%%@CRLF%Now generating diagnostic report...%@CRLF%%@CRLF%Thank you!")
If SESDay == "" Then DateError = "Blank Day Detected at Line 117"
If SESDay == "" Then SEDateGuess = ""
If SESDay == "" Then ToDate = ""
If SESDay == "" Then CalFrom = ""
If SESDay == "" Then CalTo = ""
If SESDay == "" Then Goto SESysReport
If SESYear == "" Then Message ("ERROR!", "Date Error Detected!%@CRLF%%@CRLF%Now generating diagnostic report...%@CRLF%%@CRLF%Thank you!")
If SESYear == "" Then DateError = "Blank Year Detected at Line 117"
If SESYear == "" Then SEDateGuess = ""
If SESYear == "" Then ToDate = ""
If SESYear == "" Then CalFrom = ""
If SESYear == "" Then CalTo = ""
If SESYear == "" Then Goto SESysReport

; GUESS!
SEDateGuess = StrCat (SESMonth, " ", SESDay, ", ", SESYear)
IsThisRight = AskYesNo ("Date Verification","%SECountry% date configuration found! Please confirm the following dates:%@CRLF%%@CRLF%The date extracted from your trace.log file is %FromDate%%@CRLF%%@CRLF%Is that date %SEDateGuess%?%@CRLF%%@CRLF%Yes = That's correct! Let's go to the log parser!%@CRLF%No = Create a diagnostic report and Exit%@CRLF%Cancel = Just Exit...")
If IsThisRight == @YES Then Goto BuildINI
If IsThisRight == @NO Then FileCopy ("%LogPath%trace.log", "%TempFiles%\OK_TO_DELETE_Merged Log.txt", @FALSE)
If IsThisRight == @NO Then Goto BEDates

The last section labeled "GUESS!" produces this confirmation dialog.

This is a one-time calculation and once the user verifies that the date is correct, then I write everything to an ini file for future use.

After that (and subsequent executions) they go directly to the main interface.