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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Frustrating datetime conversion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Menorel
Starting Member

USA
15 Posts

Posted - 07/08/2013 :  12:10:35  Show Profile  Reply with Quote
I have a field that is of type varchar that contains a date in the format (yyyy/mm/dd) I would like to filter the records of this field and get only the dates that are for the previous day. The issue I am having is that when I convert the string to date time I am getting Mmm d yyyy and having an issue gettig it to convert/cast to a format that I can use to compare it to they system datetime for just the date.

What is the best way to conqure this? Converting the string to a date or converting GetDate to a usable string, both being in a format where I can filter with it.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/08/2013 :  12:41:54  Show Profile  Reply with Quote
best thing is to use a datetime field for storing dates.
FOr conversion you need to make sure the date formats in the field is consistent. Based on that you need to apply a corresponding style value for CONVERT



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

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/08/2013 :  13:35:13  Show Profile  Reply with Quote
Dates stored as varchar, ack!

As Visakh suggested you can use teh CONVERT fuction using the 111 style:
DECLARE @DateString VARCHAR(20) = '2013/06/30'

SELECT CONVERT(DATETIME, @DateString, 111)
Unfortunatly, SQL won't be able to take advantage of an index. However, if you wanted rows for only the previous day then you could use something like:
DECLARE @Foo TABLE (DateAsString VARCHAR(20))

INSERT @Foo VALUES
	('2013/06/01'), 
	('2013/06/02'), 
	('2013/06/03'), 
	('2013/06/01'), 
	('2013/06/02'), 
	('2013/06/04')

DECLARE @CurrentDate DATETIME = '2013-06-03'

SELECT *
FROM @Foo
WHERE DateAsString = CONVERT(VARCHAR(10), DATEADD(DAY, -1, @CurrentDate), 111)
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/15/2013 :  09:04:25  Show Profile  Reply with Quote
Got this resolved by actually having the exporter configure the date format the way we needed it from the start.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/16/2013 :  00:59:33  Show Profile  Reply with Quote
quote:
Originally posted by Menorel

Got this resolved by actually having the exporter configure the date format the way we needed it from the start.


Good
Always better to fix it at the source if you can and make it consistent

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.05 seconds. Powered By: Snitz Forums 2000