SQL Server – How to Format Month with Two Digits

querysql server

how can i make monthid column values appear as example: 201901, not 20191.

INSERT INTO [dbo].[table2](accountno, monthid, active)
SELECT accountno, CONCAT(YEAR(InsertionDate), month(insertiondate)), active 
FROM [dbo].[table1]

Best Answer

DECLARE @t TABLE(InsertionDate datetime);
INSERT @t(InsertionDate) VALUES(GETDATE()), ('20190125');

SELECT CONVERT(char(6), InsertionDate, 112) FROM @t;

Result:

202004
201901

Related Solutions

Sql-server – Partition tables based on month and year in SQL Server

Firstly, it must be said that using SQL's native table partitioning support is preferred. But that requires SQL Enterprise, which is a lot of money, so, presumably, here we are.

A few things for you to check:

Partition column must be part of primary key
Partition column must have one and only one CHECK constraint
Partitions must not overlap. Perhaps you have a typo, or my eyes fail me, but your constraints on sales_1 and sales_2 overlap.
Make sure the constraints are checked (WITH CHECK CHECK CONSTRAINT). Given your definition in the question, it probably isn't the issue, but it is in general a necessary troubleshooting step (take a look at SELECT name, is_not_trusted FROM sys.check_constraints)

EDIT

Apparently DATEPART doesn't work. Probably you should just specify the dates directly (s_date >= '2014-01-01' AND s_date < '2014-06-01'), etc. You could also use persisted custom columns.

See: https://stackoverflow.com/questions/11155758/partitioned-view-not-working

Some other things to note about partitioned views, most of which I ran into quickly:

IDENTITY columns will prevent inserts. What I did to get around this was create a "staging" table with the same structure (and an identity column), and add a trigger that just inserts into the partitioned view and deletes from the staging table. At some point I will look into using SQL 2012's SEQUENCEs
You can't run an update/delete query on the PV if it has a self-join on the PV or any of its tables
All columns have to be specified in an INSERT statement. DEFAULT doesn't work or help. You can't omit nullable columns or columns with a default value.
If you use replication, it tends to disable the constraints (NOT FOR REPLICATION), so you'll have to make sure that doesn't happen

You can see here at the bottom: http://technet.microsoft.com/en-us/library/aa933141(v=sql.80).aspx

SQL Server – Composite Foreign Key Not Verifying All Columns

A constraint of FOREIGN KEY will never forbid you from entering NULL values in nullable columns. The constraint is simply ignored for such rows.

You can make the columns not nullable, if you want to enforce this. Based on the comments though, you probably just need to add a CHECK constraint on the table, so the two columns are either null or not null, together:

ALTER TABLE dbo.Table2 
  ADD CONSTRAINT Num_and_Dat_are_Null_Together 
  CHECK ( Num IS NULL AND Dat IS NULL 
       OR Num IS NOT NULL AND Dat IS NOT NULL
        ) ;

Best Answer

Related Solutions

Sql-server – Partition tables based on month and year in SQL Server

SQL Server – Composite Foreign Key Not Verifying All Columns

Related Question