13
votes
Differences between .dump and .sql in pg_dump PostgreSQL
The file extension means nothing. At all. It's just a part of a file name.
If you want a custom-format dump for use with pg_restore use -Fc as an argument to pg_dump.
pg_dump defaults to generating ...
- 55.6k
13
votes
How to export the output of sp_AskBrent?
Make the alert run a job that runs the EXEC msdb.dbo.sp_send_dbmail with the @query parameter:
EXEC dbo.sp_send_dbmail @profile_name = 'mail_profile'
, @recipients = '[email protected]'
, @...
- 69.7k
10
votes
Can't get my SQL query to filter by dates like I want
!= doesn't support a list of items like that.
Try one of these (note the commas between items):
WHERE Format([Start Date], 'MM/yyyy') NOT IN ('07/2016','08/2016','09/2016',...)
or even better (and ...
- 9,954
10
votes
Accepted
Does lightweight pooling disable built-in CLR facilities?
Yes.
When running in fibre mode, everything that needs the CLR is unavailable, including AT TIME ZONE.
For example, both:
SELECT FORMAT(GETDATE(), N'd', N'en-us');
and:
DECLARE @g geometry;
SELECT @g;...
- 83k
6
votes
Accepted
Getting year DATEPART from Unix date
Assuming the column is an int, and not a varchar column, you could do this:
datepart(year, c.cko_date + 25567)
25567 is the number of days between 1900-01-01 and 1970-01-01; essentially this adjusts ...
- 69.7k
5
votes
Txt format from Bank
I worked on a similar project, and we asked the source institution to provide a breakdown of the field types, lengths, and delimiters. We then stored the source files as-is for auditing, and processed ...
- 3,673
4
votes
Accepted
Insert custom header row in BCP output
You can create a CMD script to create a temp header row file, run BCP, and then append the BCP output to the temp header file. This would be called via xp_cmdshell, just like the existing call to BCP ...
- 68.3k
4
votes
Insert custom header row in BCP output
One option you might consider is separating the two. Create one file with your export and all fields. The other file just has your header. The last step would be to combine the two with something like ...
- 81
4
votes
Accepted
Query result in a daily date from a date range selection Postgres 9.3
All that you have to add is the day (be it the start or the end date). This can be obtained using a cast to date, for example. When you add it to the query, you'll need a GROUP BY clause, too, on ...
- 31k
4
votes
Accepted
How to store international phone numbers in PostgreSQL?
Generally speaking, the presentation layer is where formatting should occur. If spaces are only useful for presentation to the user, then reformat the phone number in the client app, and remove the ...
- 69.7k
3
votes
Format for recording RGB color in MySQL
If "you want to send it to the client with the same format", then it is reasonable to treat it as "opaque".
That is, store it as a string with no meaning or structure that you care about. And it ...
- 77k
3
votes
Concatenating Strings & Using FORMAT Together - NOT WORKING
It doesn't work because you are attempting to concatenate the last two characters after specifying the column alias. Move the AS VARCHAR to after the + CHAR (10).
You currently have:
'Posted on ' + ...
- 68.3k
2
votes
Match phone numbers efficiently
The right thing to do is to fix the data as it goes in. Then you have only digit strings in a 'canonical' format. This includes prefixing with the country code. (However, you may want to keep the ...
- 77k
2
votes
Accepted
Cast all boolean fields in a SELECT * query to integer
PostgreSQL doesn't have a display type for output. Using libpq you have two options:
Binary
Text
You don't get to customize how it delivers text back. And, it wouldn't make much sense either way. ...
- 62.2k
2
votes
Accepted
Why does formatting a time with AM/PM specifier return null?
Edit: tt is a valid format specifier for datetime, but not time. The \ is also optional in datetime, but not time. Example at end.
I think the tt is the problem
I am not finding tt for time ...
- 5,043
2
votes
How to get SQL Server 2012 to use the invariant culture in format()?
Use 'iv'. Works on 2017, and should run on any version, because it's connected with .NET not with SQL Server itself.
If the culture argument is not provided, the language of the current session is ...
- 21
2
votes
PostgreSQL Query - how to subtract timestamps and format durations (with builitin functions or otherweise)
This should get you started (a fiddle with the code below is available here):
CREATE TABLE sleep
(
patient_id SMALLINT NOT NULL,
sleep_date TIMESTAMPTZ NOT NULL,
time_to_bed TIMESTAMPTZ NOT ...
- 29.5k
2
votes
Accepted
How to alter tables safely to set row_format as Compressed or Dynamic in Mariadb Galera Cluster?
Since you are not really changing any data nor the schema, just the underlying row_format, seems like RSU would be better, and a lot less invasive.
You might also take each node out of the cluster ...
- 77k
2
votes
Name of SQL programming style where KEYWORDS | CRITERIA are justified to centering line
A possible name for the programming style you are looking for would be vertical aligned.
However, there is no actual standard as to how the vertical aligned style would be implemented into whatever ...
2
votes
Name of SQL programming style where KEYWORDS | CRITERIA are justified to centering line
GUI tools have/might have formatting options which allow you to set your own preferences. For example, in TOAD, it looks like this:
In Oracle SQL Developer:
Million years ago, when we were on Oracle ...
- 1,011
1
vote
How to change the SQL Server Backup file (.bak) file default icon?
On Windows 10
Right click on the .bak file
Choose properties
On the General tab, click the Change... button next to Opens with:
Under Other Options choose More apps
Scroll to the bottom and click ...
- 111
1
vote
Update selected data from same table column as currency
You really want to store data in fields of the correct Data Type.
If these values are monetary amounts, then hold them in a suitable Data Type, like Currency.
Any time you convert data from one ...
- 8,269
1
vote
Reference for check correct culture in sql server for Format function
If you did not find any other solution, you can validate using below procedure.
CREATE PROCEDURE dbo.ValidateCulture
@culture sysname, @is_valid BIT = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
...
- 210
1
vote
Accepted
In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'
If you need to manipulate the data, you cannot use SELECT *.
If you are seeing [partnumber] coming from the table, then GIGO.
Note: MySQL does not use brackets. If the column is named `partnumber ...
- 77k
1
vote
Is there a good SSMS T-SQL code clean up / formatter plugin?
Redgate's SQL Prompt is the best I've ever used. It's not free though. ApexSQL has a free tool (ApexSQL Complete), but I've never used it...looks like it does the same sort of thing.
- 539
1
vote
Default value format of a stored procedure parameter(of datatype : money)
I checked the Decimal symbol in the Regional settings and changed it from ',' to '.'
Now,the CREATE PROCEDURE display it with '.' instead of ','
Thank you for your suggestions!
- 11
1
vote
Why does formatting a time with AM/PM specifier return null?
[Formatted memo time] returns a null - for all rows
The behavior is descried here. TL;DR:
SQL Server datatype time is mapped to .NET datatype TimeSpan; so the rules for standard and custom TimeStamp ...
- 441
1
vote
Why does formatting a time with AM/PM specifier return null?
tt is the indicator for AM/PM
Paparazzi, I replaced your last two lines as follows:
select format(convert(datetime, @TimeChar), N'hh:mm tt')
select format(convert(datetime, @TimeChar), @FormatString)...
- 562
1
vote
How to get SQL Server 2012 to use the invariant culture in format()?
I tested the approaches noted in both Kevin's answer and user3792838's answer, using both the built-in T-SQL FORMAT function as well as the equivalent .NET / SQLCLR function (using the SQL# library, ...
- 68.3k
Only top scored, non community-wiki answers of a minimum length are eligible