Convert m-d-yyyy hh:mm:ss to yyyy/mm/dd in sql

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 2012 Forums

Transact-SQL (2012)

Convert m-d-yyyy hh:mm:ss to yyyy/mm/dd in sql

Author

Topic

ElMucho.Dingdong
Starting Member

2 Posts

Posted - 2014-06-18 : 18:19:50

Hi SQL gurus, I created a parameter called @startdate in sql reporting services report. Its datatype is date/time (m-d-yyyy hh:mm:ss). I have a column called insertdate in table called Atable and its datatype is datetime (yyyy/mm/dd hh:mm:ss). I wrote the followings but still got nothing but error:

select * from Atable where cast(insertdate as date) >= cast(@startdate as date)

select * from Atable where cast(insertdate as date) >= cast(convert(datetime,@startdate) as date)

Thanks in advance.

Got Weedz?!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-18 : 18:22:01

Please show us the error. You should not need to cast/convert.

select * from Atable where insertdate >= @startdate

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-19 : 10:56:18

You question doesn't make any sense as Date types don't have a format (they have range, precision, etc.). You can format a date when converting it to a string or other data type. So, are you saying reporting services needs the date as a string and you need to convert the date value to a string in a particular format? If so, look at the COVERT function and specify a style:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

ElMucho.Dingdong
Starting Member

2 Posts

Posted - 2014-06-20 : 10:19:43

Hi Folks. I should have provided more detail.
So the Atable has
Name (Varchar(20)) insertdate (datetime) enddate (datetime)
------------------ --------------------- -----------------------
Lowmein 2008-08-08 08:08:08.000 2009-09-09 09:09:09.000
Highmein 2010-01-10 10:10:10.000 2011-11-11 11:11:11.000
Midmein 2012-12-12 12:12:12.000 2013-01-01 01:01:01.000

The @startdate is calendar control parameter which data type is Date/Time. It is in format of m/d/yyyy hh:mm:ss. 6/20/2014 09:01:57.000.
How can I convert the @Startdate format to be the same format of insertdate?

the two provided sql statement returned no record. What did I miss?
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-20 : 11:51:16

Again, date data types don't have a format. But if you want to convert your date to a sting you can use CAST/CONVERT, FORMAT or string concatenation. Here is a FORMAT version:

DECLARE @Foo DATETIME = SYSDATETIME();

SELECT FORMAT(@foo, 'yyyy/mm/dd hh:mm:ss')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-20 : 11:57:10

It sounds like reporting services is the issue. I don't know how you are using it to access SQL Server. But, your's and Tara's queries look fine. If the data types are Date datatypes, there shouldn't be an need for a conversion. So, again, I'd look at what SSRS is doing. Here is a quick sample to show there is no need to convert anything to a string in SQL:

DECLARE @Atable AS TABLE (Name Varchar(20), insertdate datetime, enddate datetime)

INSERT @ATable VALUES
('Lowmein', '2008-08-08 08:08:08.000', '2009-09-09 09:09:09.000'),
('Highmein', '2010-01-10 10:10:10.000', '2011-11-11 11:11:11.000'),
('Midmein', '2012-12-12 12:12:12.000', '2013-01-01 01:01:01.000')


DECLARE @StartDate DATETIME = '20040101'

SELECT 
	*
FROM
	@ATable
WHERE
	insertdate >= @StartDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources