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.
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/2013I 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); |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
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 #accountFROM [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 #salesFROM (SELECT [FDMSAccountNo],[Submit_Date],[Net_Trans],[Net_Sales],ROW_NUMBER() OVER (PARTITION BY [FDMSAccountNo] ORDER BY Submit_Date DESC) AS NFROM [FDMS].[dbo].[stg_Daily_Sales]) s WHERE N=1order by Submit_Date desc --Updating Last Post date with daily sales last post date -- UPDATE acSET [Last_Post_Date] = [Submit_Date] FROM #account AS acleft JOIN #sales AS sales ON sales.FDMSAccountNo = ac.FDMSAccountNoselect * from #account--drop table #account,#sales |
|
|
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 acSET [Last_Post_Date] = [Submit_Date] FROM [FDMS].[dbo].[Dim_Outlet] AS acLEFT JOIN #sales AS sales ON sales.FDMSAccountNo = ac.FDMSAccountNoSELECT * FROM [FDMS].[dbo].[Dim_Outlet]Note: If you want we can do complete your script with one single UPDATE statement--Chandu |
|
|
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 acSET ac.[Last_Post_Date] = CONVERT(VARCHAR(10),CAST(sales.MaxDate AS DATE),112)FROM [FDMS].[dbo].[Dim_Outlet] AS acleft JOIN (SELECT [FDMSAccountNo] , MAX([Submit_Date]) MaxDate FROM [FDMS].[dbo].[stg_Daily_Sales] GROUP BY [FDMSAccountNo]) AS salesON sales.FDMSAccountNo = ac.FDMSAccountNo;select * from [FDMS].[dbo].[Dim_Outlet]; --Chandu |
|
|
|
|
|
|
|