DATE formatting issue - SQL Server Forums

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

DATE formatting issue

Author

Topic

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 07:57:38

Hi guys,

I'm trying to pull a list of cases where the date they were logged is between @startdate AND @enddate.

@StartDate AND @EndDate are both parameteres that are defined before running the report and aren't static values so the user and pick the timeframe to search in.

at the moment i have it working but it is SEARCHING for US dates which is a pain for us british folk :)

can someone enlighten me as to how to get it searching (and preferrably outputting) in UK dates?

here is what I have:

SELECT Case.CreatedOn
FROM MSCRM.dbo.Case Case
WHERE Case.CreatedOn BETWEEN @StartDate AND @EndDate
ORDER BY Case.CreatedOn

Thanks,
M

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 08:02:36

When you are providing the dates to the query, use the ISO format (YYYYMMDD) for setting @StartDate and @EndDate which will always be interpreted correctly.

If you want a date to be formatted correctly, more often than not, it is better to do it in the client application such as a user interface. But if you have to, you can do it in SQL using the appropriate conversion format. For example, for UK format, CONVERT(varchar(32),yourDateColumn,103).

Take a look at this page, it has very useful info: http://msdn.microsoft.com/en-us/library/ms187928.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-04 : 08:02:38

Which datatype is the CreatedOn column?
It it's DATETIME or similar, there is no need to worrying.

N 56°04'39.26"
E 12°55'05.63"

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 08:51:37

Thanks for the help guys.

so far I have tried a load of different combinations and here is the outcome.

The CRM database stores the values as:
2009-04-21 09:18:30.000

When I specify the parameter (Using visual studio) it will submit as 03/12/2010 - when i click view report it will change it to 12/03/2010 and go and fech the data.

when the data is displayed, it will show as:
7/27/2010

What I need is to input my @StartDate as 03/04/2011
(@EndDate is set to =GETDATE())

then it should return all values logged yesterday & today AND display those values as 03/04/2011.

at the moment it is pulling dates from months ago due to the formatting and when it displays the data its showing in US format (7/28/2010)

Here is what i am using:

DECLARE @Today DATETIME; SET @Today =GETDATE()
SELECT Case.CustomerName, Case.CreatedOn
FROM MSCRM.dbo.Case Case
WHERE Case.CreatedOn BETWEEN @StartDate AND @Today
ORDER BY Case.CreatedOn

Hope this is enough info.

Thanks,
M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 08:56:03

whats the language setting for the report? have you set it for US language format? also check regional and language format for the system using the report.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 09:58:49

All languages are set to UK.
is there a command that can be ran to set language to uk?
I know you can do SET LANGUAGE us_english - is there a UK version of this command?

I just find it strange how it is taking a UK date and then converting it to US fora query. I must be missing something simeple.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 10:01:23

if you want date to be interpreted in us format set the language setting also as english united states.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 10:09:34

I don't want anything US at all.

The Date in the CRM database is UK format (2009-04-21 09:18:30.000).
I specify the parameter (@startdate) as 02/01/2011 (2nd Jan 2011)
while executing the report.

it will then come back with a list of all cases logged from 01/02/2011 (1st Feb 2011).

it will display the dates for all of the matching results(Case.CreatedOn) as 02/28/2011

so somewhere between the CRM DB and the end result of this report; the UK date is being converted / formatted to US date.

I have tried a ton of stuff from MSDN and loads of SQL tutorials but i can't put my finger on it.

Thanks,
M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 10:17:31

you have british english also as a option.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 11:09:23

Excluding the issue above, if i run the query:

DECLARE @Today DATETIME; SET @Today =GETDATE()
SELECT Case.CustomerName, Case.CreatedOn
FROM MSCRM.dbo.Case Case
WHERE Case.CreatedOn BETWEEN @StartDate AND @Today
ORDER BY Case.CreatedOn

it produces the output below:

03/22/2011 8:12:17 AM Microsoft Corporation

can anyone show me how i should ammend my query to give this output:

22/03/2011 Microsoft Corporation

Thanks again.
M

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 11:53:28

You could do this, to get exactly what you are asking, but if you are consuming this data in a client application such as C# code or SSRS, it would be better to do the conversion there


DECLARE @Today DATETIME; SET @Today =GETDATE()
SELECT Case.CustomerName, CONVERT(VARCHAR(10),Case.CreatedOn,103)
FROM MSCRM.dbo.Case Case
WHERE Case.CreatedOn BETWEEN @StartDate AND @Today
ORDER BY Case.CreatedOn

When you do this, you are converting the datetime data type to character data type - which comes with its own baggage when you want to consume that data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 12:37:58

one question, is this issue happening only in report viewer or both in report manager and viewer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-04 : 15:15:58

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

CASE is a reserved word and it is singular. A table name has to be plural or collective since it is a set of things.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-04 : 17:03:54

quote:
Originally posted by jcelko

<snip> A table name has to be plural or collective since it is a set of things.
--CELKO--
<snip>

Not according to ISO-11179 Standards. Rather quite the opposite.

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 18:52:46

@sunitabeck - Thanks for your reply - I will give this a try when i get in tomorrow :-)

@jcelco - Hi, I am new to these forums and I'm also very new to SQL.
I have provided what code I have, what the current output of the query is and what I would like the output to be.
Unfortunately (being new to SQL) I do not know what DDL is or know 'if i can change it'. I am unable to change the data in the database or the structure of the database / tables.
All i am looking to do is change the format of the outputted date from 01/29/2010 to 29/01/2010. I have had a few excellent replies from the other members of the forum and will be trying these tomorrow.
If there is something vital that I have missed that doesn't allow you to answer my question like the others have then please; let me know.

I'm not looking for someone to 'do my job for me'. I am asking questions so I can learn from them and although you may not be a mind reader you could try to read between the lines when it comes to newbies ;-).

Thanks again guys, much appreciated.

duffy202
Starting Member

12 Posts

Posted - 2011-08-05 : 08:24:47

Hi Guys,

After much searching, I managed to get it sorted.
I followed SunitaBeck's advice and formatted it from within SSRS using format code:

=Format(Cdate(Fields!CreatedOn.Value),"dd/MM/yy")

for anyone interested, I managed to find the correct code on the MSDN forums at:
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/f94b66ea-5bf0-4259-a857-899690ef87ee

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources