Formatting Date and Time in SQL Server 2008

In SQL Server Management Studio, I am working on a query that involves NVARCHAR values in ISO date format. To display the date in a specific format, you can utilize the FORMAT function and specify the desired format.

Question:

One of the columns in my table is a varchar type, but it is used to store datetime values.

To avoid using varchar, I am generating a fresh column. To duplicate a current date, I utilize.

SELECT CONVERT(Datetime,entryTime,101)

The original date is

'7/13/2015 2:40:44 AM'

After using convert function I get


'2015-07-13 02:40:44.000'

What formatting should I apply to make it resemble the initial version?


Solution 1:

Consider utilizing the convert function instead of cast. However, if the date format is incorrect, then attempt selecting an alternative region.

Check out this informative post on date/time that I find really helpful. Take a look and see how it can assist you.


Solution 2:

Try this,

DECLARE @Date AS DATETIME='7/13/2015 2:40:44 AM' --Original Date
DECLARE @UpdateDate AS DATETIME=''
SELECT @UpdateDate = CONVERT(DATETIME ,@Date ,101)  --Converted Date
--SELECT @UpdateDate 
SELECT CONVERT(VARCHAR(10) ,@UpdateDate ,101) +' '+CONVERT(VARCHAR(8) ,@UpdateDate ,108) ExpectedDate --As per your expected result.

With AM/PM try below,

SELECT CONVERT(VARCHAR(10) ,@UpdateDate ,101)+' '+CONVERT(VARCHAR(8) ,@UpdateDate ,108) 
      +' '+RIGHT(CONVERT(VARCHAR(30) ,@UpdateDate ,9) ,2) ExpectedDate --As per your expected result with AM/PM

Frequently Asked Questions

Posted in Sql