• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

SQL SERVER DATE CONVERSION

Hi,  we are currently migrating from an oracle db to sqlserver.  I am working through various queries in our HR System trying to amend date format.

I am trying to return dates in the DD/MM/YYYY format.  This I can do for the sysdate no problem with the following:

//sYSTEM DATE dd/mm/yyyy
CONVERT(VARCHAR(10), GETDATE(), 103)

However, when I try to return the employee start date using:

//Convert EMPLOYEE.START_DATE YYYY/MM/DD
convert(date, cast (EMPLOYEE.START_DATE as varchar(8)),103)

The date comes back in the format: YYYY-MM-DD

Any ideas how I can return the date in the DD/MM/YYYY format for the above?

Thanks,
0
sochionnaitj
Asked:
sochionnaitj
  • 4
  • 3
1 Solution
 
lwadwellCommented:
Have you tried
CONVERT(VARCHAR(10), EMPLOYEE.START_DATE, 103)
0
 
sochionnaitjAuthor Commented:
I have, it brings back yyyyddmm

The date is held in the database as YY,YYD,DMM
0
 
lwadwellCommented:
is the date stored as a date/datetime datatype or char/varchar?  It sounds like it is char/varchar.  If so, try
CONVERT(VARCHAR(10), CONVERT(DATE,EMPLOYEE.START_DATE), 103)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
lwadwellCommented:
or is it integer? ... then maybe
CONVERT(VARCHAR(10), CONVERT(DATE,CONVERT(VARCHAR,EMPLOYEE.START_DATE)), 103)
0
 
sochionnaitjAuthor Commented:
I am getting the following

SQL Error :
-2147217913-[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit conversion from data type int to date is not allowed.

The Start_date filed is an int Type_name
0
 
sochionnaitjAuthor Commented:
Thank you that was it:

or is it integer? ... then maybe
CONVERT(VARCHAR(10), CONVERT(DATE,CONVERT(VARCHAR,EMPLOYEE.START_DATE)), 103)
0
 
lwadwellCommented:
Why are you migrating dates into an INT datatype?  Having nested convert/cast operations to change the value from INT to VARCHAR to DATE back to VARCHAR seems like a level of complexity is being added on the conversion that could have been avoided by migrating the values to a more suitable datatype in the first place.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now