Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 convert date format

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-06 : 08:36:00
Hi

Final and last question
I have a column which is
Data type : varchar(10)
Date format : 8/5/2013

I want to convert that column to the following
Data type varchar: (8)
Date format : 20130805



James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 08:49:41
Many ways to skin that cat, here is one:
SET DATEFORMAT MDY;
DECLARE @date VARCHAR(10) = '8/5/2013';
SELECT CONVERT(VARCHAR(10),CAST(@date AS DATE),112);
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 08:52:22
Hi Masond,
refer the following link for different date formats
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-06 : 08:57:38
James you are a fountain of knowledge

my end query ended up like this :)

--account build --
SELECT [FDMSAccountNo]
,[Last_Post_Date]
into #account
FROM [FDMS].[dbo].[Dim_Outlet]

-- Daily Sales build --
SELECT
[FDMSAccountNo]
,CONVERT(VARCHAR(10),CAST([Submit_Date] AS DATE),112)as [Submit_Date]
,[Net_Trans]
,[Net_Sales]
into #sales
FROM
(
SELECT [FDMSAccountNo]
,[Submit_Date]
,[Net_Trans]
,[Net_Sales]
,ROW_NUMBER() OVER (PARTITION BY [FDMSAccountNo] ORDER BY Submit_Date DESC) AS N
FROM [FDMS].[dbo].[stg_Daily_Sales]
) s
WHERE N=1
order by Submit_Date desc


--Updating Last Post date with daily sales last post date --
UPDATE ac
SET
[Last_Post_Date] = [Submit_Date]
FROM #account AS ac
left JOIN #sales AS sales ON sales.FDMSAccountNo = ac.FDMSAccountNo


select * from #account

--drop table #account,#sales
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 09:32:13
Hi masond,
There is no need of additional temp tables....
Without creating extra #account table, you can do as follows:
--Updating Last Post date with daily sales last post date --
UPDATE ac
SET [Last_Post_Date] = [Submit_Date]
FROM [FDMS].[dbo].[Dim_Outlet] AS ac
LEFT JOIN #sales AS sales ON sales.FDMSAccountNo = ac.FDMSAccountNo

SELECT * FROM [FDMS].[dbo].[Dim_Outlet]

Note: If you want we can do complete your script with one single UPDATE statement

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 09:36:49
once check this query.. This is exactly same as your script Posted - 08/06/2013 : 08:57:38
--Updating Last Post date with daily sales last post date -- 
UPDATE ac
SET
ac.[Last_Post_Date] = CONVERT(VARCHAR(10),CAST(sales.MaxDate AS DATE),112)
FROM [FDMS].[dbo].[Dim_Outlet] AS ac
left JOIN (SELECT [FDMSAccountNo] , MAX([Submit_Date]) MaxDate FROM [FDMS].[dbo].[stg_Daily_Sales] GROUP BY [FDMSAccountNo]) AS sales
ON sales.FDMSAccountNo = ac.FDMSAccountNo;

select * from [FDMS].[dbo].[Dim_Outlet];



--
Chandu
Go to Top of Page
   

- Advertisement -