0

I am querying this 

UPDATE maindb SET date  = CAST(date as DATETIME) WHERE date LIKE'%1974%'

so I'm expecting a result of 

date
1974-12-12
1974-12-13
1974-12-14

However when I query

select * from maindb where date like '%1974%'

The result is all like this

May 28 1974 12:00AM

Why is this? I remember query something like this

SET *Some code i forgot* 120

How can I set back? EDIT:
For the record my date column is in varchar(20) I don't know why but the company wants it to a new database/normalize and have 100k records. I learned that if all the data in the date column is set to yyyy-mm-dd format, you can change the column to datetime

Wowie
  • 23
  • 9
  • You're getting this "error" because your `date` field is a character type (e.g. varchar(20)). Casting a datetime to a character type defaults to format 0 (or 100). The code you're forgetting is converting it to format 120, e.g. `SELECT CONVERT(VARCHAR(20), date, 120) FROM maindb;`. See "Date and Time Styles" on [this page](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) for information about type conversion. – ZLK Apr 04 '17 at 04:31
  • Yes I know it is a varchar. but the table already have a 100k of records. So I want it to be converted to the proper one `datetime` – Wowie Apr 04 '17 at 05:49

3 Answers3

0

There are 2 issues here.

  • First, what type of field is your date field? If it is really datetime, then it will normally display the date and time, as you observe.

  • Second, although the date type is simpler than a full datetime type, the effective time is midnight at the beginning of the day. So, if you cast a simple date field to a datetime type, then you will get the date plus midnight.

If you really want to output the date as you list, then use cast date as date). To make the intention clear, you could use cast "date" AS date, to make clear that the first use of date is the column name.

As regards setting the data back to the old values, welcome to the wonderful world of SQL, where there is no undo. SQL Server does have the ability to restore to a certain extent, but it’s not always easy.

It’s a little confusing to talk about, since date refers to a field type as well as the name of one of your columns.

If you want to retrofit a corrected version of the date into an existing character field, you can try:

UPDATE maindb SET date=cast(cast(date as date) as char);
Manngo
  • 14,066
  • 10
  • 88
  • 110
  • the date field is in `string` sorry if i forgot to tell – Wowie Apr 04 '17 at 05:06
  • @Wowie It’s not a good idea to use a string type. The `cast` should still work. What data do you have in your column, and what result did you want? – Manngo Apr 04 '17 at 05:18
  • Sorry but that database is from the old company. So I am migrating it to the new and ok table – Wowie Apr 04 '17 at 05:45
  • the result i dont want is this `May 28 1974 12:00AM` which is ofcourse the result of `select * from maindb` I want the proper one for the `datetime` type – Wowie Apr 04 '17 at 05:47
  • Hey I tried your `UPDATE maindb SET date = cast(date as date) WHERE date LIKE'%1973%'` and it worked! I don't know why but the `convert(date, datetime)` doesn't? – Wowie Apr 04 '17 at 05:55
  • I don't know man but your edited answer gives the same result. – Wowie Apr 04 '17 at 06:01
0

DATETIME datatype in SQL has time component added to it. So, when you cast your datatype to datetime, it is initialized the time to 12:00 AM. To fix your issue:

  1. Create a new column with date type link to add column
  2. cast the existing column as date datatype into the new column UPDATE maindb SET date = CAST(date as DATE) WHERE date LIKE'%1974%'
  3. Drop the old column link to drop column
  4. Rename the new column link to rename column
Community
  • 1
  • 1
Nikhil Mathew
  • 56
  • 5
0

First add one column and store converted dates

 select *, convert(datetime, date) as new_date from maindb  WHERE date LIKE'%1974%'

Then update into main table to the respective column

UPDATE maindb
 SET date  = new_date

or

UPDATE maindb
 SET date  =  convert(datetime, date, 100)  WHERE date LIKE'%1974%'
Chanukya
  • 5,833
  • 1
  • 22
  • 36