MelvinsWebStuff My Online Notepad

SELECT CONVERT(VARCHAR(8), GETDATE()-365, 112)

This is a way to query and get the date in the following format:

YYYYMMDD

I came across some dates saved in the database in this format as a varchar and had to use this to initially get the date and then convert it from and integer to varchar.

View Standard Date Formats

Error: Back up the transaction log for the database to free up some log space.

To recover from a full transaction log

If the log is 100% full you cannot truncate it by backing it up, since the backup has to be recorded in the transaction log. For version 6.5, use:

DUMP TRANSACTION dbName WITH NO_LOG

And for version 7 or later:

BACKUP LOG  WITH NO_LOG

Since you cannot continue to perform transaction log backup after this command has been executed, you should perform a database backup.

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.

Example

CREATE TABLE #CustomerNames
(
	CustomerId int,
	CustomerName varchar(25)
)	

INSERT INTO #CustomerNames
SELECT 1, 'Bob Johnson'
UNION ALL
SELECT 2, 'Al Riley'
UNION ALL
SELECT 3, 'Mike Henry'

SELECT RIGHT(CustomerName, 5) FROM #CustomerNames

Results
hnson
Riley
Henry

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = ANY
(SELECT pro_no
FROM vtm_data_combination
WHERE (total_weight = 0) AND (total_charges = 0) AND (total_pieces = 0)))

This query is essentially, imagining PRO_NUMBER is a 3 digit number:

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = 097) OR (PRO_NUMBER = 647) OR (PRO_NUMBER = 324)…..etc.

Until all PRO_NUMBER are selected from vtm_data_combination.

Another way to go about changing the data type varchar to decimal is to change the varchar to money then convert the money data type to decimal. It may not be the best way to do it but it has work for me.

The function returns 1 for numeric values, and 0 for non-numeric values.

CREATE TABLE #CustomerNames
(
	CustomerId int,
	CustomerName varchar(25)
)	

INSERT INTO #CustomerNames
SELECT 1, 'Bob Johnson'
UNION ALL
SELECT 2, 'Al Riley'
UNION ALL
SELECT 3, 'Mike Henry'

SELECT ISNUMERIC(CustomerID), ISNUMERIC(CustomerName) FROM #CustomerNames

Results
1     0
1     0
1     0

Here’s a way to convert a VARCHAR to DECIMAL, wrap the CONVERT in a CASE and IsNumeric function to make sure that it doesn’t cause errors when the varchar data is non-numeric

DECLARE @test VARCHAR(25)

--Numeric
SET @test = '2.55'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

--Non-Numeric
SET @test = 'Bob'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

Results
—————————————
2.55

—————————————
0.00

Creating

CREATE PROCEDURE sp_GetRecord
@pro_num varchar(10)
AS
SELECT *
FROM DATA_DETAILS
WHERE pro_number = @pro_num

Executing

EXECUTE sp_GetRecord '16571565'