RadhaKrishnaKiJaya
asked on
Need help with a query.
Hi Experts,
I have the date in yyyymmdd format in the table. But I want to display it in mm/dd/yyyy. I am trying the following. But it does not work.
What can I do?
Thanks.
Select JobDate, convert(varchar(10),jobdat e,101) from jobdetails
I have the date in yyyymmdd format in the table. But I want to display it in mm/dd/yyyy. I am trying the following. But it does not work.
What can I do?
Thanks.
Select JobDate, convert(varchar(10),jobdat
You could use Substring to grab the MM DD and YYYYY
Declare @myDate as varchar(8)
set @myDate = '20161025'
select SUBSTRING ( @myDate ,5 , 2 )+'/'+SUBSTRING ( @myDate ,7 , 2 )+'/'+SUBSTRING ( @myDate ,1 , 4 )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As Russ Suter has rightly stated dates to NOT get stored in a format, and the fact that your existing approach does not work suggests the column isn't a real date as far as the database is concerned. So it is something else that looks like a date.
yyyymmdd
20161027 that column could be an integer or string (e.g. char/varchar)
If you do not know the actual data type of that column you will not know how to handle it properly.
This article may help:
DATE and TIME ... don't be scared, and do it right (the first time)
no points please
yyyymmdd
20161027 that column could be an integer or string (e.g. char/varchar)
If you do not know the actual data type of that column you will not know how to handle it properly.
This article may help:
DATE and TIME ... don't be scared, and do it right (the first time)
no points please
I m not sure if you have SQL 2012 or not..If you have then Format is the best thing you can use.
--FOR SQL 2012+
DECLARE @DT AS VARCHAR(10) = '20160101'
SELECT FORMAT(CAST(@DT AS DATE),'MM/dd/yyyy')
GO
O/P
01/01/2016
--FOR SQL 2008+
DECLARE @DT AS VARCHAR(10) = '20160101'
SELECT CONVERT(VARCHAR(10),CAST(@ DT AS DATE),101)
GO
O/P
01/01/2016
Hope it helps!!
--FOR SQL 2012+
DECLARE @DT AS VARCHAR(10) = '20160101'
SELECT FORMAT(CAST(@DT AS DATE),'MM/dd/yyyy')
GO
O/P
01/01/2016
--FOR SQL 2008+
DECLARE @DT AS VARCHAR(10) = '20160101'
SELECT CONVERT(VARCHAR(10),CAST(@
GO
O/P
01/01/2016
Hope it helps!!
VB approach
Private Function ParseDate(dateString As String, format As String) As Date?
Dim dt As Date
If Date.TryParseExact(dateString, format, Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dt) Then
Return dt
End If
Return Nothing
End Function
Private Function FormatDate(dt As Date, format As String) As String
Return dt.ToString(format, Globalization.CultureInfo.InvariantCulture)
End Function
Using:
Dim dt = ParseDate("20160215", "yyyyMMdd")
If dt.HasValue Then
MsgBox(FormatDate(dt.Value, "MM/dd/yyyy"))
Else
MsgBox("Invalid date")
End If
Try...
Dim provider As CultureInfo = CultureInfo.InvariantCultu re
Dim dt As Date = DateTime.ParseExact("20160 215", "yyyyMMdd", provider).ToString("MM/dd/ yyyy")
Dim provider As CultureInfo = CultureInfo.InvariantCultu
Dim dt As Date = DateTime.ParseExact("20160
ASKER
Thanks. It worked!!
Radha - Just out of curiosity, were your values stored in a column with date format or varchar?
As you can tell that was a big part of the answer that was not mentioned in the original question, and I don't see any follow-up from you before declaring 'it worked'.
Thanks in advance.
As you can tell that was a big part of the answer that was not mentioned in the original question, and I don't see any follow-up from you before declaring 'it worked'.
Thanks in advance.
ASKER
It is Varchar. I just tried Russ's solution and it worked for me.
Open in new window