Tag Archives: datetime

An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

Coding, Data, Database Administrator, Development, How To, sql, SQL Server, T-SQL, , , , , , , , , ,

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

An icon depicting a calendar and clock

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

Batch Script, Coding, , , , , , , , ,

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)

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.