Frustrating datetime conversion

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

SQL Server 2005 Forums

Transact-SQL (2005)

Frustrating datetime conversion

Author

Topic

Menorel
Starting Member

15 Posts

Posted - 2013-07-08 : 12:10:35

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

52326 Posts

Posted - 2013-07-08 : 12:41:54

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-08 : 13:35:13

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)

Menorel
Starting Member

15 Posts

Posted - 2013-07-15 : 09:04:25

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 00:59:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources