Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

SQL specifying data and time

I have a table with an index value, date,time and a text field. There are constraints on the date and time fields. The constraints are:
DATE1 - ((datepart(hour,[DATE1]) = 0 and datepart(minute,[DATE1]) = 0 and datepart(second,[DATE1]) = 0 and datepart(millisecond,[DATE1]) = 0))
so the date must be like 20210812 00:00:00.000

TIME1 - ((datepart(day,[TIME1]) = 1 and datepart(month,[TIME1]) = 1 and datepart(year,[TIME1]) = 1900))
and the time must be like 19000101 08:23:50.010

What is the proper way to construct this insert statement? I will have values for index and text and can use GETDATE() but how do I format GETDATE()?
Avatar of ste5an
ste5an
Flag of Germany image

Those constraints make not really sense. Cause normally you would choose DATE in the first case and TIME in the second case. Then you don't need them.

But the entire post sounds like you're having a different actual problem. Maybe you consider to rephrase your post and give use more context.
Avatar of rwheeler23

ASKER

This table is a table provided by Microsoft and no changes are allowed. It is simply a notes table with date and time stamp. Here is the table creation script.

CREATE TABLE [dbo].[SY03900](
    [NOTEINDX] [numeric](19, 5) NOT NULL,
    [DATE1] [datetime] NOT NULL,
    [TIME1] [datetime] NOT NULL,
    [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
    [TXTFIELD] [text] NOT NULL,
 CONSTRAINT [PKSY03900] PRIMARY KEY NONCLUSTERED
(
    [NOTEINDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[SY03900]  WITH CHECK ADD CHECK  ((datepart(hour,[DATE1])=(0) AND datepart(minute,[DATE1])=(0) AND datepart(second,[DATE1])=(0) AND datepart(millisecond,[DATE1])=(0)))
GO

ALTER TABLE [dbo].[SY03900]  WITH CHECK ADD CHECK  ((datepart(day,[TIME1])=(1) AND datepart(month,[TIME1])=(1) AND datepart(year,[TIME1])=(1900)))
GO

Here is some sample data.
NOTEINDX    DATE1    TIME1    DEX_ROW_ID    TXTFIELD
2873.00000    2011-03-18 00:00:00.000    1900-01-01 10:45:19.000    1    Trip to NYC
179.00000    2011-03-18 00:00:00.000    1900-01-01 10:45:52.000    2    Needed to hail a cab

I need to format the date and time as you see above.
I could do something like this but is this the proper way?
INSERT INTO [dbo].[SY03900]
           ([NOTEINDX]
           ,[DATE1]
           ,[TIME1]
           ,[TXTFIELD])
     VALUES
           (10148391.00000
           ,CONVERT(DATE,GETDATE())
           ,'19000101 '+CONVERT(CHAR(12),CONVERT(TIME(0),GETDATE()))
           ,'')
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial