SQL Converting dates

Transact-SQL
1

I have below date that want to convert to SQL but not working?? Not sure how to formulate this?

FFDT=TO_CHAR(TO_DATE('2016-11-02', 'YYYY-MM-DD'), 'YYYY') --- Oracle

FFDT = Convert(varchar(10),CONVERT(DATETIME, '2016-11-02'), 'YYYY')--SQL

FFDT is a date

Thanks!
Pasi

2

IF FFDT is defined as a date column

FFDT = CAST('2016-11-02' AS DATE)

3

First - FFDT is actually a string...not a date. If I am reading this correctly:

TO_DATE() - returns a date based on the input, so this is returning '2016-11-02' as a date.
TO_CHAR() - returns the string representation in the format specified. So taking the date value of '2016-11-02' and returning the string value of YYYY gives us '2016' as the string value.

In SQL Server - if you want a string value:

CAST(YEAR('2016-11-02') AS CHAR(4))

If you don't need the value as a string then just use YEAR('2016-11-02'). If the input column is a string formatted as YYYY-MM-DD you don't have to convert it to datetime - it will implicitly be converted. However, you really should not be storing dates as char/varchar in SQL Server.

4

Thanks! I have another column that is just year like "2017", is the conversion the same?
Pasi.

5

What are you converting the value to - and why are you converting it?

6

Well I am trying to understand what is the reason behind these in Oracle since the Calendar_year already in format YYYY,. These reports were written in oracle by someone some long ago and trying to make sense of it. and trying to convert them to SQL.

This is how it shows in Oracle:

image
image.png742×771 69.8 KB

7

running this query CAST(year('2016-11-02') AS CHAR(4))

I dont get the YYYY I get the whole date

image

8

There is something else going on...the YEAR function returns the year of the date passed into it...the cast just converts the return value to a string as CHAR(4).

If you put the result of that into a DATE variable or column - it will implicitly be converted to {YEAR}-01-01.

The code you are looking at:

select calendar_year
from employee
where rownum < 10
and calendar_year = TO_CHAR(TO_DATE('2016-11-02', 'YYYY-MM-DD'), 'YYYY')
and erncd='53'

This code is pulling the calendar_year column from the employee table where that column = 2016. The column calendar_year appears to be a string data type - but you would have to confirm that on the Oracle system.

Either way - the column in SQL Server should be set to either INT or CHAR(4) depending on how you want to use the column.

1 Like
9

Thanks for tips I 'll see what I can do.
Pasi.

10

Hello

he format used to convert between data types, such as a date or string format. Can be one of the following values.

Best Perdisco Assignment Help in Australia

11

Hello

for select any date use calender's in SQL.