CODEWITHSUNDEEP
sqlt-sql
Shahsra
Shahsra

Reputation: 1009

conversion of datetime to getdate

I have a problem in converting a column with datetime and need to convert into getdate.

For example column A (datetime, null) and that column contains value some right values with getdate and some values have something like this '1900-01-01 15:32:00.000'. I need help in changing the values to regular getdate values. And some fields in that columns has NULLS also.

Thanks in Advance, Shashra

Upvotes: 0

Views: 820

Answers (2)

jadero
jadero

Reputation: 800

A few things:

GetDate() is a function that returns the current date and time. Yes, it includes the current time.

The example you provide looks like a 'pure' time (i.e. no date component available). If that is real data, then somehow you're getting decimal values of the form 0.#### recorded in that field. Of course, you could have real data from that date or you might have just invented that example for this question.

Finally, if you what you really want is to get rid of the time component so that you have a 'pure' date, try convert(datetime,convert(nvarchar(50),getdate(),101)). The inner convert creates text in the form mm/dd/yyyy and the outer convert turns that back into a date of the form yyyy-mm-dd 00:00:00.000

Upvotes: 1

gbn
gbn

Reputation: 432261

  • You wouldn't store the output of GETDATE() if you want it to be different every time. You'd do SELECT col1, col1, GETDATE() AS Now FROM Mytable...

  • There is no safe way to distinguish a "real" vakes from a "false" value without having an arbritrary condition such as "anything on the last month is OK". "1900-01-01 15:32:00.000" is a valid datetime value

If you mean "ignore values where only a time bit, not date (01 jan 1900) then what about:

SELECT 
  col1, col2, 
  CASE WHEN DateTimeCol <> '1900-01-01 15:32:00.000' THEN DateTimeCol ELSE GETDATE() END,
  CASE WHEN DateTimeCol > '1901-01-01' THEN DateTimeCol ELSE GETDATE() END,
  ...

Upvotes: 1

Related Questions