SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 two date formats in same column		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

155 Posts
Posted - 08/20/2012 :  17:41:18  Show Profile  Reply with Quote
In one of my table, we import data exported from two different sources.

so the date column has two formats one is 'dd/mm/yyy' and the other one is 'yyyymmdd' in the same column and the data type is varchar(50).

Now when I write a query like this, it doesn't include data with 'yyyymmdd' format.


select convert(varchar(8),convert(datetime,date,103),112) as Date,
sum(qty1), sum(qty2), sum(qty3) from table1
where convert(varchar(8),convert(datetime,date,103),112) = '20120701'


As you see in my code, i want to display it in format 'yyyymmdd' and even in where conditions i want it to be 'yyyymmdd'.

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/20/2012 :  17:47:07  Show Profile  Reply with Quote
the best thing to do is to convert all of them to same format and if possible make datatype as datetime

UPDATE t
SET t.[date]=CONVERT(varchar(11),CONVERT(datetime,t.[date],103),112)
WHERE t.[date] LIKE '%/%/%'


will make all of them in same yyyymmdd format

then you can do alter table..alter column to make it datetime


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

155 Posts
Posted - 08/20/2012 :  17:52:36  Show Profile  Reply with Quote
thanks visakh16. I know this is valuable suggestion..but the problem will be then I will have to update this every time new data is imported as I cannot change the format in source while exporting from it.

And I guess if I use any table trigger for such small things, it will make further my importing process slow. Every time we import at least 20,000 data.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/20/2012 :  18:01:54  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

thanks visakh16. I know this is valuable suggestion..but the problem will be then I will have to update this every time new data is imported as I cannot change the format in source while exporting from it.

And I guess if I use any table trigger for such small things, it will make further my importing process slow. Every time we import at least 20,000 data.




why not include convertion logic in import application then to bring all of them in the same format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

155 Posts
Posted - 08/21/2012 :  01:49:19  Show Profile  Reply with Quote
That application is not controlled by us.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/21/2012 :  01:52:04  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

That application is not controlled by us.

then only way is a trigger. check the format inside trigger using logic and convert all of them to same consistent format. Make field also datetime in that case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000