CODEWITHSUNDEEP
sql-serverdatabaset-sql
user6305775
user6305775

Reputation: 57

SQL Server: how can I insert current date into database?

SQL Server: how can I get current date with the following format?

I have the following code to insert date to table but how can get CURDATE() in the formats listed below

 INSERT INTO [MIBOMH] 
   ([bomItem], [bomRev], [rollup], [mult], [autoBuild], [assyLead],[revCmnt],[author],[descr],[qPerLead],[lstMainDt],[revDate],[effStartDate],[ovride] )
 SELECT DISTINCT [bomitem], [bomrev], '1', '1', '1', '3','SYNC ','AUTHORNAME','SYNC','0', '2016-07-12 14:10:19.427','20160712','20160712','0'
FROM [DB].[dbo].[whlmibomh]
  WHERE  [bomitem] IN (SELECT [ItemID] FROM [MIITEM] WHERE type='2')
 AND [bomitem] NOT IN (SELECT [bomItem] FROM [MIBOMH])

Desired formats:

  1. '2016-07-12 14:10:19.427'
  2. '20160712'
  3. '20160712'

Upvotes: 0

Views: 106

Answers (1)

Rubens Farias
Rubens Farias

Reputation: 57946

You should go with FORMAT function:

DECLARE @d DATETIME = GETDATE();  
SELECT  FORMAT(@d, 'yyyy-MM-dd HH\:mm\:ss\.fff', 'en-US') AS 'Format#1'  
       ,FORMAT(@d, 'yyyyMMdd'                  , 'en-US') AS 'Format#2'

Upvotes: 1

Related Questions