Character Date String

[jovillanuev]

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

[adam_k]

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

[jovillanuev]

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.

[Eagletalon]

Try the following?:

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

[adam_k]

Eagletalon is right. I should have removed the AS ReceivedMonth part.

[jovillanuev]

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

[Eagletalon]

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

[jovillanuev]

Here is the string format for ReceivedDate

This is the fields -- ReceivedDate nvarchar(24)

Output  --   '5/28/2011'

[Eagletalon]

It runs perfectly for me?

QUERY:

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

RESULTS:

May

[adam_k]

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.

[Eagletalon]

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

[adam_k]

jovillanuev has the dateformat worked for you?

[jovillanuev]

Thank you very much guys...

Regards,

Jov