0

I'm inserting data into a table and before inserting I need to check if data exists.

I have a composite key consisted of two columns of datetime and int.

Before inserting I need to check if the data with the same time and id exists in the table.

The date that user is inserting is in 'mm/dd/yyyy'.

The datetime data in the table looks like this: '2016-01-12 00:00:00.000'. The id field is int.

So, I have a query:

if not exists(select count(*) from table_1 where MyDate = @myDate and id = @id)
insert into table_1 .....

What is the right way to format the date user sends to match the datetime format in the table?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
gene
  • 2,098
  • 7
  • 40
  • 98
  • 1
    Which RDBMS is this for? It often does make a difference whether you're using MySQL, PostgreSQL, Oracle, SQL Server or IBM DB2 - or something else even. Please add a relevant tag to your question! – marc_s Feb 21 '16 at 14:55
  • Also, it's worth mentioning that [Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Feb 21 '16 at 15:42

3 Answers3

0

Check this sqlfiddle about how to use different date formats in your query. Might help you to solve it.

http://www.sqlfiddle.com/#!2/fd0b7/5

Tushar
  • 3,022
  • 2
  • 26
  • 26
0

I am guessing that the question is about SQL Server, based on the syntax. The issues in the code snippet far transcend date formats.

First, the expression:

if not exists(select count(*) from table_1 where MyDate = @myDate and id = @id)

will never return true, because the subquery always returns one row with one column. If nothing matches, the column contains 0, which does exist.

You intend:

if not exists(select 1 from table_1 where MyDate = @myDate and id = @id)

Second, this check is not necessary if you wisely choose to have the database enforce the uniqueness constraint. So, define a unique index or constraint on the two columns:

create unique index unq_table_1_id_mydate on table_1(id, MyDate);

Now, the database won't let you insert duplicate values and no if is necessary.

Next, I would suggest that you fix the date format at the application layer. YYYY-MM-DD is an ISO standard date format and quite reasonable. However, if you don't want to do that, use convert():

insert into table_1(id, MyDate, .....)
    select @id, convert(datetime, @MyDate, 101), . . .

The value in the database looks to be correct stored as a date/time value, so this should work fine.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That n you for your answer, but I need to use the table which is given to me and defined without a unique index – gene Feb 21 '16 at 15:06
0

You can use following line to convert date to required format in SQL server:

select FORMAT(@your_date, 'yyyy-MM-dd HH:mm:ss', 'en-US') from Your_Table
sAm
  • 319
  • 2
  • 20