Tag Archives: datetime

How to create a datetime stamp within a batch script in the format YYYY-MM-DD

Below is a batch script that populates the variable Timestamp with a datetime value in the format YYYY-MM-DD_HH:MM:SS.

This technique can then be used when creating files etc.

To try out the example below copy the text and paste it into a text editor like notepad. Save the file as timestamp with the extension .bat and then double click on the filestamp.bat file.

This code example will display the current datetime in windows command shell. It will also create a blank file called Test along with the current datetime in the format yyyymmddhhmmss in the same directory the batch script is run from.

@ECHO off
CLS
ECHO Date format = %date%

REM Breaking down the format 
FOR /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G
ECHO dd = %datetime:~6,2%
ECHO mth = %datetime:~4,2% 
ECHO yyyy = %datetime:~0,4%
ECHO/
ECHO Time format = %time%
ECHO hh = %time:~0,2%
ECHO mm = %time:~3,2%
ECHO ss = %time:~6,2%
ECHO/

REM Variable format 1
SET Timestamp=%date:~6,8%-%date:~3,2%-%date:~0,2%_%time:~0,2%:%time:~3,2%:%time:~6,2%
ECHO New Format 1: %Timestamp%
ECHO/
REM Variable Format 2
SET Timestamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%
ECHO New Format 2: %Timestamp%
ECHO/
REM Building a timestamp from variables
SET "dd=%datetime:~6,2%"
SET "mth=%datetime:~4,2%"
SET "yyyy=%datetime:~0,4%"
SET "Date=%yyyy%%mth%%dd%"
ECHO Built Date from variables: %Date%
ECHO/

REM Write Timestamp into file name
REM BREAK>Test%Timestamp%.txt 
PAUSE

 

How to get the hour from a datetime string (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.