MS SQL Hyper-V Replication

If you are running MS SQL on Hyper-V, I highly recommend that you enable hyper-V replication. Hyper-v replication is recommended for DR (disaster recovery), however, you can use it if you have two servers within the same network/data-center.

When enabling replication, you must have a valid SSL certificate if you do NOT have an active directory. So . . . → Read More: MS SQL Hyper-V Replication

MS SQL how to replace line feed or carriage return with space

SQL 2000, SQL 2005 and SQL 2008

You can replace carriage return and line feed in a field by running the following query:

update tbl1 set field1 = replace(field1,0x0a,’ ‘)

update tbl1 set field1 = . . . → Read More: MS SQL how to replace line feed or carriage return with space

SQL 2005 MM-DDD-YY format

To format a date as MM-DDD-YY

REPLACE(right(UPPER(convert(varchar(20),getdate() ,06)), len(convert(varchar(20),getdate(),06))), ‘ ‘, ‘-‘)

to get Yesterday’s date:

PRINT REPLACE(right(UPPER(convert(varchar(20),getdate()-1 ,06)), len(convert(varchar(20),getdate()-1,06))), ‘ ‘, ‘-‘)

Extra date format for Microsoft SQL:
Continue reading SQL 2005 MM-DDD-YY format

SQL breakdown TIME

This statement will count number of records in a given month and breakdown it every 30 minutes.

select count(ID) as ID_Count, Date_30min from
( select ID, convert(datetime,convert(varchar(14),calldatetimefield,120) + case when datepart(minute,calldatetimefield) < 30 then '00' else '30' end) as date_30min from tablename where calldatetimefield between '2009-02-01 00:00:00.000' and '2009-02-28 23:59:59.997' and ......) . . . → Read More: SQL breakdown TIME

SQL updating field with current timestamp

MS SQL to update a field to current time:

update tbl1 set col1 = convert(varchar(5),getdate() ,108)


col1 . . . → Read More: SQL updating field with current timestamp