I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is JS0002. I've been playing around with format but it doesn't seem I can do this if I put my EmployeeID as an autonumber. Thanks in advance!

I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its format. Id date 1 01-07-2014 2 02-07-2014 3 03-07-2014 4 05-07-2014 5 07-07-2014 6 14-07-2014 7 18-07-2014 8 19-07-2014 9 21-07-2014 10 01-08-2014 11 02-08-2014 12 03-08-2014 On the php file $from = '01-07-2014'; $to = '02-08-2014'; I need to update some values from all the dates that are between 01-07-2014 and 01-09-2014 to the format dd-mm-yyyy. I am using UPDATE successlog SET successlog.overtime ='00:00' WHERE date >= '$from' AND date <= '$to' It is not working. I tried using the key between $from and $to. This does not work either. When the format was yyyy-mm-dd it was working normally, but after I changed the format to dd-mm-yyyy, it is not working.

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux (Ubuntu) based solution. I have seen a online version but nothing as a installable. Eclipse Based IDE's are a plus as well Example: select f1, f2, fname, lName from tblName where f1 = true and fname is not null order by lName asc to something like SELECT f1, f2, fname, lName FROM tblName WHERE f1 = true AND fname IS NOT NULL ORDER BY lName ASC Here is a online example: - http://www.dpriver.com/pp/sqlformat.htm But I would rather this be in a local environment Related: - https://stackoverflow.com/questions/3310188/free-sql-formatting-tool UPDATE: Looking at this: - https://github.com/darold/pgFormatter FINAL UPDATE: While this might be an overkill JetBrains has a database IDE, DataGrip , which has some great re-formatting options.

I have a series of files I've downloaded from the [Texas Ethics Commission](https://www.ethics.state.tx.us/dfs/search_CF.htm) (a misnamed . You can see an example of their [layout in the ReadMe.txt](https://www.ethics.state.tx.us/tedd/CFS-ReadMe.txt) . I've made an [tag:ETL] library which processes the [ReadMe.txt](https://www.ethics.state.tx.us/tedd/CFS-ReadMe.txt) to generate the [SQL DDL](https://en.wikipedia.org/wiki/Data_definition_language) to create this schema, and load it from the CSVs. The thing is, I *think* this is a standardized format. I imagine it's backed by something like a PICK (which is a BASIC database) or something COBOL-esque -- having worked with them before, and that this is something like a [MARC](https://en.wikipedia.org/wiki/MARC_standards) , or ANSI, or ISO standard. I'd like to potentially abstract out my ETL script to benefit others who use this format. Some identifying features of the format are that it supports * Arrays and internal one-to-many relations on the record * at least types BigDecimal, Long, Date, String * the export is labeled "Flat File Architecture Record Listing" Arrays ===== For example here you see this, Array 4050 loanGuarantorLoanPersent[5/ROW_MAJOR] CsvPublicExportLoanGuarantorLoanPersent 810 Guarantors for the loan (maximum 5) 46 guarantorPersentTypeCd String 30 Type of guarantor name data - INDIVIDUAL or ENTITY 47 guarantorNameOrganization String 100 For ENTITY, the guarantor organization name 48 guarantorNameLast String 100 For INDIVIDUAL, the guarantor last name That defines a structure called a loanGuarantorLoanPersent and essentially declares that there are five of them. So the export CSV will have something like guarantorPersentTypeCd1,guarantorNameOrganization1,guarantorNameLast1,guarantorPersentTypeCd2,guarantorNameOrganization2,guarantorNameLast2,guarantorPersentTypeCd3,guarantorNameOrganization3,guarantorNameLast3... You can see an example of this data here, * https://github.com/EvanCarroll/db-Texas-Ethics-Commission/blob/master/data/TEC_CF_CSV/ReadMe.txt * https://github.com/EvanCarroll/db-Texas-Ethics-Commission/blob/master/data/TEC_LA_CSV/LobbyLAR-ReadMe.txt

"Often, you get the right outcome without doing so." Example: select CONVERT(varchar, getdate(), 112) outputs 20240417 I saw this in quite a few places on Stack Exchange until I found a tiny remark that told the reader not to forget the length in brackets. I claimed that this is not needed if you set the style number for a datetime like this. Yet, I got enough insight now to understand that "varchar should never stand alone", it should always be written as something like varchar(1234). As for the code above, that would be: select CONVERT(varchar(8), getdate(), 112) If you look at it, the output is the same: 20240417 Why should you still always write varchar with the length in brackets behind it? #### PS: If you can, take even char(8) instead of varchar(8) / nvarchar(8) A remark below went even further: char(8) should be taken since: > no value in style 112 will ever be less than 8 characters. #### Links that bring up this question This is just a random list of some links where I saw varchar() without brackets. There are many more out there. - [Convert date yyyy-mm-dd to integer YYYYMM - DBA SE](https://dba.stackexchange.com/a/106900/212659) - [SQL Server Convert Varchar to Datetime - Stack Overflow](https://stackoverflow.com/a/10247180/11154841) - [Convert date to number data type - Stack Overflow](https://stackoverflow.com/a/63625407/11154841) - [How to get a date in YYYY-MM-DD format from a TSQL datetime field?](https://stackoverflow.com/q/889629/11154841) - [Convert date to YYYYMM format](https://stackoverflow.com/a/14217859/11154841) - [SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value](https://stackoverflow.com/a/20840132/11154841)

I am not a professional, sorry. But perhaps you can help me anyway ... I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus spool, I have several problems. I tried the advices given here: https://dba.stackexchange.com/questions/54149/how-to-make-sqlplus-output-appear-in-one-line , but they don't help me. I'll explain below. First the configuration of the SQL plus query: set term off set echo off set underline off set colsep ';' set linesize 32767 set pages 10000 set trimspool on set trimout on set feedback off set heading on set newpage 0 set headsep off ;set wrap off set termout off set long 20000 spool D:\tmp_Datenaustausch\Export-141204.csv SELECT MEDIENNR, ISBN, ISBN2, ISBNSUCH, KATKARTE1, BUCHKARTE1, SIGNATUR1, KATKARTE2, BUCHKARTE2, SIGNATUR2, KATKARTE3, BUCHKARTE3, SIGNATUR3, MAB2, FEHLERCODE, FARBE, ANZSYKKARTEN, KKSTATUS, BKSTATUS, MABSTATUS, SESTATUS, FARBSTATUS, ASSTATUS, REZENSION, REZENSENT, STICHWORTE, UPDDATE FROM Bekartungsdaten WHERE upddate like TO_DATE('2014-12-01', 'yyyy-mm-dd'); spool off; exit; / **My problems:** 1. Headings are not in one line, "set wrap off" does not help, because it truncates data. 2. Some fields contain text with linebreaks, sql+ puts the lines of these fields in seperate fields in the csv. I need them in one field with linebreaks like stored in the database. 3. How do I get quotationmarks at the beginning and end of each field/cell in the csv? I would be very glad, if some could help me!

Is there a name for this kind of SQL programming style? --Courtesy of Markus Winand of modern-sql.com/ SELECT * FROM (SELECT cities.* , ROW_NUMBER() OVER(PARTITION BY country ORDER BY population DESC) rn , COUNT(*) OVER(PARTITION BY country) ct_cities FROM cities ) t WHERE t.rn = 1 AND t.ct_cities > 1 Source: [dbfiddle](https://dbfiddle.uk/DK5X59PV) related to this topic: Oracle Idea: FIRST() and LAST() aggregate functions --------------- It's as if there is a line between the SQL keywords (right-justified) and the criteria (left-justified). Whereas in other SQL styles, the SQL keywords are left-justified. ----------------- I ask because that style seems easy to read and I want to find out more about it. Edit; a related post: SQL Developer - Keep analytic function on single line when auto-formatting

Running SQL Server in fibre mode (lightweight pooling ) disables the use of SQL CLR: > Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling". Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Policy-Based Management. On the other hand, disabling SQL CLR alone (without enabling lightweight pooling) does **not** disable the built-in CLR types like geometry, and geography (though hierarchyid is mentioned above), as shown in https://dba.stackexchange.com/q/77608 Now some new language features rely on the CLR, for example the FORMAT function : > FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR). Does running SQL Server in fibre mode disable the FORMAT function and/or using the CLR types?

Clients use custom culture to view the website for themselves. This culture setting is saved per user in the database, and this setting is accessed using the format field in the select query as follows:

DECLARE @TimeZone VARCHAR(31) = (SELECT name FROM sys.time_zone_info AS TZI WHERE TZI.current_utc_offset = '+03:30')
DECLARE @Ctype VARCHAR(5) = 'fa' -- 'de-de' or 'en-US' or 'zh-cn'

SELECT 
Format( CONVERT(datetime,U.CreatedDate AT TIME ZONE 'UTC' AT TIME ZONE  @TimeZone  ) ,'yyyy/MM/dd | hh:mm',@Ctype)
FROM UserSetting AS U

How to find @Ctype with a correct refrence. I use sys.time_zone_info system table for saving valid time zones and checking user changes. For checking **user data entry** validation, I **need a reference for culture type**. We need a dynamic reference so that all valiad cultures are applied to SQL Server because some users can set not valiad cultures. I can't find useful document in microsoft about Format culture. I need **a sys table or other reference for find valiad culture** in sql server 2019, our base language for develop is tSQL. for set table as follows:

Declare @UserId int = 1
Declare @TimeZone sysname, @CultureType sysname

-- validation
IF NOT EXISTS (SELECT 1 FROM sys.time_zone_info AS TZI WHERE TZI.name = @TimeZone )
  Return 'False time zone'
IF (@Culture NOT IN ('fa','en','ar','en-us')) -- THIS IS MY PROBLEM, I need check all true cultures
  Return 'False culture'


INSERT INTO UserStting (UserId,TimeZone,Culture)
Values (@UserId,@TimeZone,@CultureType)

This article is helpful for finding all cultures in sql-server-helper , but I need a better way, whitout hard code, like timezone table.

**This is a bad question since I only made a mistake in selecting by chance a client that had garbage data and must have changed the where condition during tests, and it was also a problem of a join that I did not even mention in the question that led to so many of the strange [partnumber] entries that the limit 10 query showed *only* those. So that I thought the whole column would have this value. I am not sure whether I should delete it. It might also help someone else who has garbage data without knowing about it. You may vote to close the question if you think it has wasted your time. For I see myself that this can be a waste of time reading.** In a normal SELECT p.* FROM table query with many columns, the output shows values in a needed column, every number shows up, showing the column with the partnumber (a partially numeric number like p-12345) only: But when I ask for only that column alone with SELECT client_x FROM table, I get: ## UPDATE: I can see the values of that column now!! They are not showing [partnumber] anymore in each row, but instead, there are numbers and strings and empty values as well in it. I had the where condition on the client in it, and the value in that client is always [partnumber]. It is garbage only in that client. ## UPDATE end The column data type is varchar(100). It should be treated as a normal string. I use DBeaver, but that should not play a role. If I concatenate the column with some string, the value appears as an empty string: SELECT CONCAT("test", client_x) FROM table returns just "test" as values. How can I avoid this [partnumber] output in the column output and show the part number string instead, like it is shown when I just run SELECT * FROM table?

I am trying to generalize a trigger that deletes the oldest rows in a table as new rows are added. Since the number of rows to retain depends on the table, I am passing that number to the trigger function as an argument. When I use format() to interpolate the table name, it appears I am losing access to new, as described in [Trigger function taking column names as parameters to modify the row](https://dba.stackexchange.com/questions/127787/trigger-function-taking-column-names-as-parameters-to-modify-the-row) . Do I require hstore to accomplish this, or is there a simpler approach? Current code:

create or replace function drop_over_n()
    returns trigger
    language plpgsql as $$
        begin
            execute format('
                with chrono_by_usr as (
                    select id, row_number()
                    over (partition by usr_id order by created_at) as row_number
                    from %I
                    where usr_id = new.usr_id and id != new.id
                )
                delete from %I
                where id in (
                    select id
                    from chrono_by_usr
                    where row_number > %s
                );
            ', tg_table_name, tg_table_name, tg_argv);
            return null;
        end;
    $$;

create or replace trigger limit_recent_finding
    after insert on recent_finding
    for each row execute function drop_over_n(50);

I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration. ### Sleep Table Here is the select statement I have so far:

select wakeuptime,hour from
		(
			select patientid, 
                   sum(s.duration) as hour, 
                   Date(s.wake_up_time) as wakeuptime 
            from sleep s
			where patientid = pid::varchar
			group by patientid, Date(s.wake_up_time) 
			order by Date(s.wake_up_time) desc
		) s
		order by s.wakeuptime asc;

I need to calculate Sum (wake_up_time - time_to_bed) where it should convert minutes into hours for example 55mins + 15mins should return 1hr 10mins.

I am running MySQL 5.6.51. And I notice that there is MySQL 8.x. Based on https://severalnines.com/database-blog/tips-for-upgrading-mysql-5-7-to-mysql-8 , I need to first upgrade from 5.6.x to 5.7.x, then to MySQL 8.x. My question is, during the first phrase or second phrase of upgrade, will the database in my server also be upgraded as well? Or need I upgrade the database format to match the version of MySQL manually?

In PostgreSQL, I can cast individual boolean fields to integer when I want to get 0/1 output instead of f/t. SELECT some, thing, mybool::integer FROM mytable; But is there a way to ask PostgreSQL to do it for any boolean field in the query, without specifying the field name? If I have: SELECT * FROM mytable; I would like any bools to be output as 0/1. In this particular case, I'm using psql. Didn't see anything in it's options or pset settings, so I hoped there might be some global config in the database, or some obscure SQL syntax which might do it.

I am using: - SQLite version 3.21.0 ---------- I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot. Though, now I am wondering if there are ways to: 1. Set a column width to be equal exactly to the width of its header? I went through the corresponding section of the documentation — the closest thing it mentions is .width 0, which effect is explained as follows: > If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. if it was only *'the maximum of **two** numbers: 10 and the width of the header'* — it would suffice my needs, though *'...and the width of the first row of data.'* spoils it for me. 2. Is there a way to set width of all columns to be the same, without knowing in advance how many columns are there? E.g. instead of doing .width 5 5 5 for 3-column SELECT, I would like to do something like .width all 5.

We have some tables with row_format=Compact. The biggest table is around 30Gb, so how safely i can alter tables and set the row format as Compressed for the big table and Dynamic for other three tables which are under 10GB each? It is a cluster with three nodes with mariadb v10.4.12 and Galera 4. Running DDL / alter tables and change the row format can leads to cluster blocking and maybe it took long time! Can anyone help and guide me how i can do it? Do you think using RSU mode helps here and is it safe since we talking about changing the row format?

Is there a mysql command to change the output format of a table? Specifically, I'd like to get rid of the formatting '-'s and '+'s: +------+------------+ | ID | Name | +------+------------+ | 1523 | Wien | | 1524 | Graz | +------+------------+ I tried: -------- Appending --result-format=tabbed to my docker exec command: docker exec -it [DB-container-name] mysql -uroot -p --result-format=tabbed as I gathered from this MySQL page , but this gives me "unknown variable".

With my query: SELECT (CAST(0.00 AS MONEY)) I am expecting a result : 0.00 But it shows me the result: 0,00(with comma) I checked this query ,when i found that,the CREATE PROCEDURE script displays the default value as [money] = 0,0000, for one of the input parameters of a procedure. How can i make the CREATE PROCEDURE display it with '.' instead of ','?

I'm looking for a good plugin that will automatically do things like upper case all keywords, indent lines appropriately, maybe brackets around reserved words being used as names / aliases, etc.

I have a query where a formatted expression is returning null: declare @FormatString nvarchar(max) = N'hh:mm tt' select M_STARTTIME as [Memo Time 1] , convert(time,M_STARTTIME) as [Memo Time 2] , format(convert(time,M_STARTTIME), @FormatString) as [Formatted Memo Time] , convert(time, getdate()) as [Current Time] , format(getdate(), @FormatString) as [Formatted Cur Time] from MEMO where M_STARTTIME is not null *M_STARTTIME* holds an *nvarchar* field with values such as '10:27', '13:01', '8:25' and null. In this query: 1. [Memo Time 1] displays the string field 2. [Memo Time 2] displays the memo time as a time field correctly 3. [Formatted memo time] returns a null - for all rows 4. [Current Time] shows the current time 5. [Formatted Cur Time] shows the current time formatted using the format specifier. According to MSDN , FORMAT returns null for errors other than a culture that is not valid. For example, null is returned if the value specified in format is not valid. I have not been able to identify where the error is though (I'm assuming i've missed something really obvious, or the issue is something really subtle). Converting the *nvarchar* to a time should not be the problem, the time displays correctly in column 2. Formatting using the time specifier should not be the problem, since I use the same time specifier on another time value. Interestingly, according to example D in the MSDN link (time values) that time format specifier should cause an error because the ':' is not escaped. If that is the case why is *[Formatted Cur Time]* working? Also, if I escape the format specifier with: declare @FormatString nvarchar(max) = N'hh\:mm tt' then the [Formatted Memo Time] still returns null and the *[Formatted Cur Time]* still returns the current time. dbcc checktable (MEMO) ...identified no issues What is going on?