how to convert the date in the view? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to convert the date in the view?

Author

Topic

argon007
Starting Member

38 Posts

Posted - 2008-06-06 : 00:20:28

quote:
Run the view for orders shipped from January 1, 2003 and June 30, 2003, formatting the shipped date as MON DD YYYY.

creating the view:

CREATE VIEW vw_orders
AS
SELECT
o.order_id,
c.customer_id,
c.name AS 'customer_name',
c.city,
c.country,
o.shipped_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id

running the view:

 SELECT *
FROM vw_orders
WHERE shipped_date between '01/01/2003' and '06/30/2003'
ORDER BY 'customer_name', 'country'

I tried:

in creating view,

shipped_date = CONVERT (char(12) , o.shipped_date, 107 )

then run a view as result:

quote:
order_id customer_id customer_name city country shipped_date
---------- --------------- ------------------------------------------- ---------------- -------------------- ------------

what should i do?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-06 : 00:26:54

Hi,

try with this

SELECT *
FROM vw_orders
WHERE dateadd(day,0,datediff(day,0,shipped_date)) between '01/01/2003' and '06/30/2003'
ORDER BY 'customer_name', 'country'

argon007
Starting Member

38 Posts

Posted - 2008-06-06 : 00:31:10

thanks for answering, but that is not i want. i want MON DD YYYY

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-06 : 00:37:41

Hi,

please Refer BOL

argon007
Starting Member

38 Posts

Posted - 2008-06-06 : 00:57:03

what is bol?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 01:02:15

quote:
Originally posted by argon007

what is bol?

books online. You can use the different styles in CONVERT function to get the date in the format you want.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-06 : 01:02:38

Bol means Books on Line or search in google

argon007
Starting Member

38 Posts

Posted - 2008-06-06 : 01:03:56

ok. i will try that, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources