-1

I am working with an audit table that has an nvarchar(255) column that can have many types of different data values based on another field value. For one of those other field values I'm trying to convert the nvarchar value to a datetime value. Here is an example of what I'm trying to do:

declare @d nvarchar(255) = '2022-06-01'
SET @d = CONVERT(datetime,@d)
select @d;

The value returned is: Jun 1 2022 12:00AM

What I am trying to get is: 06/01/2022 12:00AM

I did try the code below but I'm missing the 4 digit year and I want the seconds removed but it's a lot closer than the example above:

declare @d nvarchar(255) = '2022-06-01'
SET @d = CONVERT(varchar,CONVERT(datetime,@d),22);
select @d;

The value returned is: 06/01/22 12:00:00 AM

What I am trying to get is: 06/01/2022 12:00AM

Is there a way to accomplish that? Any help/direction would be greatly appreciated. Thank you.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Melinda
  • 1,501
  • 5
  • 25
  • 58
  • Ideally, don't use ambiguous date formats (`yyyy-MM-dd` is ambiguous with `datetime`), or be explicit with a [style code](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#date-and-time-styles) when using `CONVERT`. Though you shouldn't be using `varchar` objects to store date and time values at all; the format is for the presentation value, *not* the SQL layer. Define your variable `@d` as a `date`. – Thom A Jun 15 '22 at 14:35
  • 4
    `FORMAT` is a possible option: `SELECT FORMAT(CONVERT(datetime2(0), @d, 23), 'MM/dd/yyyy hh:mmtt')`. – Zhorov Jun 15 '22 at 14:48
  • 2
    Just an aside ... varchar without a length can lead to unexected results – John Cappelletti Jun 15 '22 at 14:50
  • @Zhorov, thank you. Your solution fixed the issue I was having. I appreciate your reply. Thank you! – Melinda Jun 15 '22 at 15:07
  • you're confusing the display format with the stored value. stored as '2022-06-01' can be cast to a date, and using format() you can control how that value is displayed. Otherwise it uses ... whatever regional settings you ahve for rendering a date. – xQbert Jun 15 '22 at 18:02

1 Answers1

0

Thanks @Zhorov and @Melinda,

  • @Melinda - Glad that you have cleared the issue.
  • Based on your suggestions posting that as an answer to help the other community members who are facing with the same issue.
  • Please refer this file for Format SQL Server Dates with FORMAT Function.
  • Here is the Synatx for Format function which we need to use,
SELECT FORMAT(CONVERT(datetime2(0), '2022-06-15', 23), 'MM/dd/yyyy hh:mmtt') as Date1

SELECT FORMAT(CONVERT(datetime2(0), GETDATE(), 23), 'MM/dd/yyyy hh:mmtt') as Date2

Query with Output:

enter image description here

TechieWords
  • 92
  • 6