Insert DateTime value in dd-MMM-yyyy format in SQl Server

Sumeet
 
on Apr 16, 2018 05:05 AM
7506 Views

Sir,

 I have created a table with 3 columns as below:

dtFrom datetime NOT NULL,

dtTo datetime NOT NULL,

service_time int NOT NULL,

Now, I would need to insert datetime in dtFrom & dtTo columns. Date that I would need to insert is as 22-Mar-2018. But on inserting it keeps on saving date as 1988-03-01 00:00:00.000

I have used below query:

insert into tbl_prev_policy_issued_before(dtFrom,dtTo,service_time)
values(
       REPLACE(CONVERT(VARCHAR(11), '01 Mar 1988', 106), ' ', '-'),
       REPLACE(CONVERT(VARCHAR(11), '01 Apr 1990', 106), ' ', '-'),
       55)

How should I save the date as 22-Mar-2018 this format. ?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
nagaraju60
 
on Apr 16, 2018 05:23 AM
on Apr 16, 2018 05:27 AM

Dear @sumeet,

 

No, it isn't.
DATETIME and DATE fields ion SQL do not have a "format" any more than they do for DateTime values in C# - they are stored as a number of milliseconds since an arbitrary point in the past. Formatting is only ever applied when the date is converted to a string to display to the user - so check the code that read these values out and displays them: SSMS will normally display records with it's standard formatting (ISO 8601, or yyyy-MM-dd) unless it is specifically told not to.

But please, don't do things like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
This also means that you don't convert a DateTime value to a string, just to convert it back to a DateTime at the SQL end with all the possible errors that can involve. Just pass the dt_date.Value directly to SQL as a DataTime value via a parameter.

 

please refer below article

https://www.codeproject.com/Questions/719495/How-to-insert-date-in-dd-MM-yyyy-format-using-Csha

 

your database column datatype is datetime, so the data is stored in correct format, if you want to store your custom format than

change datatype to nvarchar(11) and try to insert

 

 

 

kalpesh
 
on Apr 16, 2018 05:42 AM
on Apr 16, 2018 05:44 AM

If Your dtFrom and dtTo Datatype is DateTime then it will not possible if both column is Varchar datatype then you can save as per your required output. Refer below sample query for your reference.

SQL

 DECLARE @dtFrom DATETIME
 DECLARE @dtTo DATETIME  
 
 DECLARE @dtFromVARCHAR VARCHAR(11)
 DECLARE @dtToVARCHAR VARCHAR(11)  
 
 SELECT  @dtFrom = REPLACE(CONVERT(VARCHAR(11), '01 Mar 1988', 106), ' ', '-') ,
         @dtTo= REPLACE(CONVERT(VARCHAR(11), '01 Apr 1990', 106), ' ', '-') ,
         @dtFromVARCHAR = REPLACE(CONVERT(VARCHAR(11), '01 Mar 1988', 106), ' ', '-') ,
		 @dtToVARCHAR= REPLACE(CONVERT(VARCHAR(11), '01 Apr 1990', 106), ' ', '-') 
		 
 SELECT @dtFrom as dtFrom ,@dtTo as dtTo,@dtFromVARCHAR as dtFromVARCHAR,@dtToVARCHAR as dtToVARCHAR

Output

dtFrom dtTo dtFromVARCHAR dtToVARCHAR
1988-03-01 00:00:00.000 1990-04-01 00:00:00.000 01-Mar-1988 01-Apr-1990