-1

Here I am trying to insert nvarchar value to datetime column after conversion ,What I want to do is ,I need to insert the data as in this format "18-05-2018 11:09:31 AM" but in my case it returns "2018-05-18 11:09:31.000"

Code :

DECLARE @SESSION_START_TIME NVARCHAR(100) = '18-05-2018 12:12:10 PM';

INSERT INTO EC_USER_SESSION (SESSION_START_DATE)
            VALUES(convert(datetime,@SESSION_START_TIME,105))

I think I were wrong in conversion ,Can anyone help me to solve this .

Benish Paul
  • 3
  • 1
  • 2
  • 2
    A datetime is a date and time, the textural representation of it on display is nothing to do with its stored value (which is semi-numeric). You are confusing display formats on selection with the storage format. – Andrew May 18 '18 at 09:52
  • try with `DECLARE @SESSION_START_TIME NVARCHAR(100) = '2018-05-18 11:09:31 AM'` if you can manipulate your input. – B3S May 18 '18 at 09:55

1 Answers1

0

Date & Time Formats are stored as integer internally, it's the difference from the date '01/01/1900'. You can display & manipulate it into different formats, but unformatted select statement will show in "2018-05-18 11:09:31.000" this format only.

If you want to store in the specified format, you need to change the datetime column to Nvarchar in your table.

Use the Query for the format you specified.

SELECT Convert(VARCHAR, convert(datetime,@SESSION_START_TIME,105),105)+ ' ' +
RIGHT(Convert(VARCHAR, convert(datetime,@SESSION_START_TIME,105), 22) ,11)
Tanveer Singh Bhatia
  • 426
  • 2
  • 8