two date formats in same column

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

two date formats in same column

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-20 : 17:41:18

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

52326 Posts

Posted - 2012-08-20 : 17:47:07

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/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-20 : 17:52:36

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-20 : 18:01:54

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/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-21 : 01:49:19

That application is not controlled by us.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 01:52:04

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources