Monthly Archives: February 2015

How to get the hour from a datetime string (T-SQL)

How To, Reporting, sql, SQL Server, T-SQL, , , , , , , , , , , , ,

Often data is entered into the database as a string (varchar), when a more appropriate data type should have been applied.

Here’s a snippet of T-SQL for dealing with datetime data, stored as varchar, when what you really need is the hour. 

DECLARE @DateTime AS VARCHAR(25)

-- GET THE CURRENT DATETIME AND CONVERT INTO A STRING
SET @DateTime = (
		SELECT CONVERT(VARCHAR(25), GETDATE(), 120)
		)

--PRINT @DateTime
-- GET HOUR FROM DATETIME AS A STRING
SELECT DATEPART(HOUR, CONVERT(DATETIME, CONVERT(VARCHAR(13), @DateTime, 120) + ':00')) AS ConvertedHour

 

Why would data be stored as a varchar instead of using the appropriate data type in the first place?

The most common reason might be that XML files have been loaded without any data type conversions being applied, i.e. dates have been entered as strings along with everything else.

Why would you need this code?

Should you be confronted with data in this raw string form you would be unable to apply a clause regarding a specific hour. Ideally there should be some sort of staging table to convert the fields into the appropriate data types but if you simply wanted to apply some clauses to select specific data, which will be outputted to an excel file for example, that additional effort may be unnecessary.

How to copy only files from a folder and respective subfolders using a batch script

Batch Script, How To, , , , , , , , , , ,

Ok so here’s a nifty little batch script that will loop through all the directories in a directory to gather all the subsequent files into a new directory. (Code at bottom of the page)

(For those of you that don’t know what a batch script is here’s a quick definition. A batch file is a text file that contains a sequence of commands for a computer operating system. It’s called a batch file because it batches (bundles or packages) into a single file a set of commands that would otherwise have to be presented to the system interactively from a keyboard one at a time.)

Here’s a diagram explaining how the batch script provided at the bottom of the page works. (Note that the folders aren’t copied just the files.)

diagram of process

So why would you use this?

Lets say you’re dealing with a system that has outputted thousands of folders into a single folder and each folder contains files.

You could be looking at millions of files to be gathered, a process that cannot be done manually.

Or a simpler use case might be you’ve downloaded thousands of movies and each was put into a separate folder. Now you just want the media files in one location.

How to use:

Open the program notepad.

Copy the code below and past it into notepad saving the file with the extension .bat

Place the batch file in the parent folder, i.e. the folder all the other folders are in.

Double click the batch file.

The batch file will create a folder one directory level above where the parent folder is located and copy all the files to this location.

An example of how to use the file is as follows:

If you had the parent folder “My Movies” on your desktop.

Pasting the batch file into this folder, and double clicking on it, would copy every movie into a folder called “Copied from My Movies” on your desktop.

And here’s the code,
Adiós.


@ECHO OFF
TITLE WAIT !
:: ASSIGN THE FILE PATH OF BATCH FILE TO A VARIABLE
SET "sourceDir=%CD%"
:: GET THE NAME OF THE FOLDER WHICH THE BATCH FILE IS IN
FOR %%a IN (.) DO SET currentFolder=%%~na
:: GO UP ONE DIRECTORY
CD ..
:: MAKE A DYNAMIC FOLDER NAME
::SET folderName=Copied From %currentFolder%
SET "folderName=Copied from %currentFolder%"
:: CREATE A FOLDER TO PUT THE COPIED FILES IN
:: IF FOLDER ALREADY EXISTS DELETE IT
IF EXIST "%folderName%" RMDIR "%folderName%" /S /Q
MKDIR "%folderName%"
:: ASSIGN DESTINATION FOLDER TO A VARIABLE
SET "destinationFolder=%CD%\%folderName%"
:: CREATE A LOG FILE IN DESTINATION FOLDER
SET "_report=%destinationFolder%\logxcopy.txt"
:: CREATE ERROR MESSAGE
IF NOT EXIST "%sourceDir%" (ECHO.Could not find %sourceDir% &GoTo:DONE)
:: OVERWRITE PREVIOUS LOG
>"%_report%" (
echo.%date% – %time%
echo.—————————————————
echo.
)
:: COPY FILES
FOR /F "Delims=" %%! IN ('DIR "%sourceDir%\" /b /s /a-d 2^>NUL') DO (
@ECHO.%%! &(
@XCOPY "%%!" "%destinationFolder%\" /i /y /h /f /c >>"%_report%",2>&1)
)
:DONE
TITLE,Done…….
ECHO.&PAUSE>NUL

view raw

batch copy file

hosted with ❤ by GitHub

A picture of a cx919 android tv stick

How to root a CX919 Android 4.4.2 Quad core with a RK31 rockchip

Android, How To, , , , , , , , , , , , , , ,

(This Post was last updated 13/02/2015)

Ok so here’s a quick tutorial on how to root this particular type of Android TV Stick (ATS). Specifically this model: See Link. If you’ve ever flashed a custom rom onto an android device before, forget everything about that, it’s nothing like that, it’s much simpler.

NOTE: you will need a windows PC to root the device and before starting make sure you have a good usb micro b cable you know works! USB Micro-B PlugIf you’ve found your way to this page you may have already tried the manual approach which uses the TPSparkyRoot batch script and that hasn’t worked! I’ve been there, you won’t be able to get it working, you were right to continue google searching your way across the net for a method that works, and here it is. . .

But first the obligatory caveat:

I take no responsibility for the welfare of your device. If by following these instructions you should brick your device, that’s your problem buddy. This tutorial will likely work for other devices too though. So if you’ve tried every other tutorial on the net to get your damned device rooted to no avail you might want to follow along. But again I’m not responsible if your ATS explodes.

Back up your device prior to implementing the instructions and you “should” be fine.

And now the tutorial:

STEP 1: Download software

Start by installing the following software:

Moborobo

Kingo

(Use the direct download link to skip downloading the download.com software)

(The reason you need moborobo is that the software is smart enough to determine what driver the PC needs to be able to communicate with the ATS. Chances are, if you tried an alternative method to root the device, the previous methods you tried to root the device failed because you couldn’t find the right driver.)

STEP 2: Prepare the ATS for communication with the PC

In the ATS go to System settings > Storage and click the 3 vertically stacked dots at the top right of the screen.

Picture showing the icon to clickClick on the window “USB computer connection” and tick the Mass Storage box.

Next within the systems settings, go to the developer options. (If the developer options are not there it is because they have not been enabled. To enable developer options go to the system settings and click on About Device. Next scroll to “Build Number” and tap it 7 times. After tapping 7 times you will see an alert saying “You are now a developer”/”Developer mode has been enabled”.

After navigating to developer options scroll down and tick the usb degugging option.

STEP 3: Connect the PC to the ATS

With the ATS connected to your TV via HDMI and powered by the DC usb port, connect the usb micro b cable to the available port.

Make sure you do not use the DC port to connect to the PC otherwise nothing will happen.

A picture showing which usb port to useNext connect the ATS to your PC via the USB.

Within system settings on the ATS click on “USB ” and tick “Connect to PC”.

STEP 4: Install the correct driver on the PC

Open the Moborobo software on your PC and wait for it to load completely. Moborobo should now create a connection to the ATS and install some apps on it. Once you see your TV screen switch to the Moborobo app you know the PC successfully connected to the device.

(You don’t need these apps once this process is run. The whole point of using moborobo was simply to install the correct driver on your PC. If the device installs the correct drivers on the PC itself you may not need moborobo to connect to the ATS, in which case continue to the next step.)

STEP 5: Rooting the device

Open Kingo on your PC. It will load for a few seconds.

When a screen appears asking if you want to root it has successfully connected to the device.

Uncheck the install APUS boost+. This is an optimization app not necessary for rooting the device.

Click root.

(Kingo may mistakenly think the device is already rooted in which case click on root again)

Kingo on desktopAllow Kingo to run.

COMPLETION:

Once you see the screens below on your PC and ATS the root was successful.

successPCsuccessATSWithin system settings on the ATS click on “USB ” and tick “Connect to PC”.

TIDYING UP:

You can now uninstall the software on your PC.

And uninstall the following Apps on your device.

  • MoboMarket
  • Mr.Clean
  • Kingo Root

Kingo will install its own SuperUser App which will need to remain but this simply acts as a checker for other apps looking to access system resources which you can either give permission to or deny.

FINAL WORDS:

Hopefully that all worked out for you.

If you used this tutorial successfully please comment below and share.

UPDATE 13/02/2015: Kingo superuser can sometimes grow to take up massive amounts of the device’s storage space. In my case over 600mb!!! Uninstalling the app from the google play store will reduce the app in size without actually uninstalling the app. (The app cannot actually be removed within settings, only disabled) In my case the app shrank to just over 5mb, much better!

Quick Caveat: There have be some online accusations that Kingo superuser steals user data. I cannot confirm this, but I’d imagine if it was a serious concern google would have removed the app from the store by now.