MS SQL insert value in datetime column

Discussion in 'Databases' started by dracula51, Oct 31, 2012.

0
  1. #1
    Hi im new in MS SQL world..

    there is a column whos datatype is datetime

    i was trying to insert value like this

    INSERT INTO tablename VALUES ('20120925');
    Code (markup):
    this insert a value like 2012-09-25 00:00:00.000
    means It successfully inserted date & leave the time default

    but i wanna insert time also along with date

    should be like 2012-09-25 19:47:00.000
    (i dont care abt the seconds & ms)

    I tried- INSERT INTO tablename VALUES ('201209251947');
    didnt work


    HELP plz ??

    PS: i dont wanna change the datetime format using SET...but leave it sql default (if possible)
     
    dracula51, Oct 31, 2012 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    VALUES ('2012-09-25 19:47')

    You may have to change the data format:
    set dateformat ymd

    (It may work as '20120925 19:47')
     
    Rukbat, Nov 1, 2012 IP
  3. sandeepdude

    sandeepdude Well-Known Member

    Messages:
    1,741
    Likes Received:
    69
    Best Answers:
    0
    Trophy Points:
    195
    #3
    Just tested a sample query in my local db...As Rubkat suggested, the format '20120925 19:47' works good..Inserts date like 2012-09-25 19:47:00.000
     
    sandeepdude, Nov 6, 2012 IP
  4. abhirampathak3

    abhirampathak3 Peon

    Messages:
    44
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    hey , you can try this hopefully it will help ..... tst before run
    i think this is the ddl:

    USE [FHPol_Lehrgang_XP_2]
    GO
    /****** Objekt: Table [dbo].[T_Teilnehmer_Ausfälle] Skriptdatum: 04/01/2008 09:22:36 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[T_Teilnehmer_Ausfälle](
    [Tln_Dienstnummer] [nvarchar](6) NOT NULL,
    [No_ID] [smallint] NOT NULL CONSTRAINT [DF__Temporary__No_ID__05F8DC4F] DEFAULT (0),
    [No_Von] [datetime] NULL,
    [No_Bis] [datetime] NULL,
    [No_Beginn] [datetime] NULL CONSTRAINT [DF__Temporary__No_Be__06ED0088] DEFAULT ('12/30/1899'),
    [No_Ende] [datetime] NULL CONSTRAINT [DF__Temporary__No_En__07E124C1] DEFAULT ('12/30/1899 23:59:59'),
    [No_Grund] [nvarchar](50) NULL,
    [No_LG_ID] [nvarchar](50) NULL,
    [No_Bemerkung] [varchar](8000) NULL,
    [No_Urlaubstage] [decimal](18, 1) NULL,
    [msrepl_tran_version] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [No_Sachb_Datum] [datetime] NULL,
    [No_Sachb] [nvarchar](50) NULL,
    CONSTRAINT [aaaaaT_Teilnehmer_Ausfälle_PK] PRIMARY KEY NONCLUSTERED
    (
    [Tln_Dienstnummer] ASC,
    [No_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[T_Teilnehmer_Ausfälle] WITH NOCHECK ADD CONSTRAINT [T_Teilnehmer_Ausfälle_FK00] FOREIGN KEY([Tln_Dienstnummer])
    REFERENCES [dbo].[T_Teilnehmer] ([Tln_Dienstnummer])
    GO
    ALTER TABLE [dbo].[T_Teilnehmer_Ausfälle] NOCHECK CONSTRAINT [T_Teilnehmer_Ausfälle_FK00]
     
    abhirampathak3, Nov 19, 2012 IP