0

Guys,

I have a Date string. My objective is how to get the Datename.
I got this Receivedate as string when I used the Bulk insert. i make the
receiptdate as nvarchar because i got an error when i used the bulk insert.

Select 
	ItemNumber,
	PurchasePrice,
	ReceivingPO,
    Cast(ReceivedDate as Datetime) as ReceivedMonth
From #VPTable as vp
Where vp.ReceivingPO is not null

Thanks.

JOv

Edited by jovillanuev: n/a

3
Contributors
12
Replies
14
Views
6 Years
Discussion Span
Last Post by jovillanuev
Featured Replies
  • Try the following?: [CODE]SELECT ItemNumber, PurchasePrice, ReceivingPO, DATENAME(MONTH,CONVERT(DATETIME, ReceivedDate)) AS ReceivedMonth FROM #VPTable as vp WHERE vp.ReceivingPO IS NOT NULL[/CODE] Read More

0

If your Cast is working, then I don't see a problem.

Select 
	ItemNumber,
	PurchasePrice,
	ReceivingPO,
   DATENAME(d,Cast(ReceivedDate as Datetime) as ReceivedMonth) as 'Day' -- Change d to whatever you need.
From #VPTable as vp
Where vp.ReceivingPO is not null
0

Thanks Adam_K.

Got an ERROR from this script..

Select 
	vp.ItemNumber,
	vp.PurchasePrice,
	vp.ReceivingPO,
    --Cast(vp.ReceivedDate as Datetime) as POMonth,
    DATENAME(Month,Cast(ReceivedDate AS Datetime) AS ReceivedMonth) AS 'POMonth' 
From #VPTable as vp with (nolock)
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
1

Try the following?:

SELECT ItemNumber,	
             PurchasePrice,
             ReceivingPO,
             DATENAME(MONTH,CONVERT(DATETIME, ReceivedDate)) AS ReceivedMonth
FROM #VPTable as vp
WHERE vp.ReceivingPO IS NOT NULL

Edited by Eagletalon: n/a

Votes + Comments
I stand corrected
0

Hi Adam_K and Eagletalon,


Here is my Data Structure. I got received an error when executing that script
coverting the date string to datetime.

Error:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.


Create Table #VPTable
(
  ItemNumber nvarchar(255),
  PurchasePrice Varchar(255),
  ReceivingPO nvarchar(50),
  ReceivedDate nvarchar(24),
)

Thank you..

JV

0

Hi jovillanuev

Error:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

This error is 1 I've had many times over... it means that the string you are using for a date is not in a valid format for SQL to interpret...

Could be something like: "201121 14:35"...

would it be possible to just select the string to see the output? give us an example of what needs to be converted

0

Here is the string format for ReceivedDate

This is the fields -- ReceivedDate nvarchar(24)

Output  --   '5/28/2011'
0

It runs perfectly for me?

QUERY:

SELECT DATENAME(MONTH, CONVERT(DATETIME, '5/28/2011')) AS ReceivedMonth

RESULTS:

May
0

It runs perfectly for me?

QUERY:

SELECT DATENAME(MONTH, CONVERT(DATETIME, '5/28/2011')) AS ReceivedMonth

RESULTS:

May

That's because your regional settings is US or any other that has MM/DD/YYYY.
If I where to run it I would get the same error (I'm Greek = DD/MM/YYYY).


jovillanuev put

SET DATEFORMAT mdy

before the query and run them together. This will tell SQL how to read the date correctly.

0

That's because your regional settings is US or any other that has MM/DD/YYYY.
If I where to run it I would get the same error (I'm Greek = DD/MM/YYYY).

Wow... there I learned something awesome :D... thnx man... oh and btw I'm from South Africa and I believe the setting fall under UK? :S nevertheless if that solves it then bloody well done, wouldn't have thought of it in years

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.